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?

Hi,

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?

Thanks!

• ###### 1. Re: Is there a way to calculate averages by excluding values that fall outside a certain threshold?

Hi Leanne, you can do this quite easily by creating a field that tests whether the field's value is outside a certain range. The field itself is quite easy. For instance, if you had a shipping days field, you could use this:

[Shipping Days] <= 5

Let's call this [Outlier Filter]. Then, you could use this as a normal filter on your entire sheet. Or, if you didn't want to filter your entire sheet for some reason, you can use calculations to exclude the outliers. For instance:

IIF([Outlier Filter], [Shipping Days], NULL)

You would average that field, of course. This would only make sense if you're making a view that has a level of detail less granular than individual shippers, though.

Now, the harder part is figuring out what your outlier is. Here's a neat trick: if you use LOD calculations, you could potentially calculate your tolerance range dynamically. For instance, let's say that you want to exclude anything above one standard deviation over the mean for all the records in your context. You could do this (note: I haven't tested this code).

[Shipping Days] <= { AVG([Shipping Days]) + STDEV([Shipping Days]) }

1 of 1 people found this helpful
• ###### 2. Re: Is there a way to calculate averages by excluding values that fall outside a certain threshold?

This is awesome - thanks, David!

I haven't tested out that last code yet, but I get the idea you're suggesting; so in the unlikely event it doesn't work, that's still a great starting point.

Thanks again!

• ###### 3. Re: Is there a way to calculate averages by excluding values that fall outside a certain threshold?

You're welcome!