1 Reply Latest reply on May 31, 2016 6:13 PM by diego.medrano

    How calc MEDIAN and AVERAGE with zero and to fill in "missing" rows in data

    Mark Smith


      I am struggling with how to figure out averages and medians and include 0s from my data set.

      There can be zeros I want to use either:

      1. when there is a record in the data showing a product quantity purchased of 0 (strange but true) [example in the attached: Customer I, Product 1)
      2. when the customer doesn't purchase the product, so I want to assign "0" quantity to them [example: Customer G, Product 1]


      A tricky items (to me):

      the same product can appear sold to the same customer multiple times, like when Customer A purchases Product 1 in both California and Washington, there is a different row for each states. But when I calculate averages and medians, I want to only calculate by customer (so use the sum of California and Washington sales).


      I am going to assume each customer has the same length contract for all of their products (even if across multiple states).


      My end goal is to have a subset of products from the data (say show just Product 1 and Product 3):

      1. Total bookings [I think I am doing this correctly]
      2. Total annualized bookings (so converting every bookings into a yearly figure, which will mean dividing by the number of years…even if the contract is only a fraction of a year, that is OK) [I think I have correct]
      3. Average annualized bookings [I couldn't figure this out, since I need to calculate the average over all of the customers, even if there isn't a row for the product, like for Customer G and Product 1..right now the average excludes that customer since there are no annualized bookings, even 0, in the data]
      4. Median annualized bookings [wrong either way I tried – using MEDIAN and also a calculated field with WINDOW_MEDIAN]
      5. Bonus one: the unit quantity that is greater than or equal to the unit quantity of 80% of the customers [zero clue how to do this]


      Then I'm going to do all of the above again, in the same worksheet (so not use a filter to toggle between the sets of customers), but for only customers with >=12 month contracts. I've started to set up those too.


      Any advice would be great. Thank you –