3 Replies Latest reply on Feb 15, 2017 12:54 PM by David Li

    Is there a way to calculate averages by excluding values that fall outside a certain threshold?

    Leanne Vermeulen



      I don't really think a worksheet is necessary, as this is more of a general and relative idea, rather than something that is specific to a certain dataset.


      Basically, I was wondering if there was a series of actions or advanced calculations that would work for calculating the average of a calculation/measure, by excluding certain values that may fall outside of a pre-defined/pre-determined "scope" or range.


      A good example of this would be if you were calculating the average days it takes to ship an item after an order has been placed, but you know that for this particular company, they generally have a good hold on things and for something like "Same Day Shipping", it would take a really rare situation for them to take longer than 5 days to ship an order under this Shipping Status. If so, you could deduce that any order where the Shipping Time took longer than 5 days would have been a "special" circumstance, in which case you would want to exclude that instance's measure from the overall Avg. Shipping Time for that period. Because you know this would have been a rare anomaly, the point is to exclude it from the overall average because the value is significantly higher than the Avg. Shipping Time of the remaining orders and you wouldn't want it to affect that average value, as it would not be a true reflection of the true average for Shipping Time.


      Obviously, it goes without saying that you would still need to be able to identify and pick up the fact that a "Same Day Shipping" order took longer than 5 days (or even 1, for that matter) but that can be taken care of separately in another worksheet. What I'm looking at specifically is whether there would be a way to define this type of exclusion in a calculation when calculating an average.


      Has anyone ever done something similar to this?