How to ignore a number well below average

[dbcooper88]

Hello board. Let say you have a range of number like 1,5,6,8. How would I ignore a number thats below a certain value below the average of said range and return that average? TIA.

 

[JoeMo]

Hello board. Let say you have a range of number like 1,5,6,8. How would I ignore a number thats below a certain value below the average of said range and return that average? TIA.

Once you have decided upon what range you will allow in your average, you can use the AVERAGEIFS function.

 

[joeu2004]

Let say you have a range of number like 1,5,6,8. How would I ignore a number thats below a certain value below the average of said range and return that average?

First, it is risky to remove "outliers" (too low or too high) arbitrarily. It can skew results, resulting in misleading conclusions. (Like who is likely to win an election. :->)

Second, there are statistical methods that professionals use, and there are ad hoc methods that the rest of us use. Which are you interested in?

IMHO, a good statistical method is to use the interquartile range (IQR). But statistically, none of the values in your example is "too low" or "too high".

A poor ad hoc method, IMHO, is to use TRIMMEAN. But TRIMMEAN removes an equal number of values from the low and high ends. It does not make sense to use it with such a small example, IMHO. (Is your example unrealistic?)

 

[dbcooper88]

Once you have decided upon what range you will allow in your average, you can use the AVERAGEIFS function.

AVERAGEIFS: For the criteria I want to put a cell value but it will only allow a numerical value. Any way around that?

 

[JoeMo]

AVERAGEIFS: For the criteria I want to put a cell value but it will only allow a numerical value. Any way around that?

Not sure I understand what you mean by "cell value", or how to average anything other than numbers. Can you clarify?

 

[dbcooper88]

Not sure I understand what you mean by "cell value", or how to average anything other than numbers. Can you clarify?

• =AVERAGEIF (range, criteria, [average_range]). My criteria is >B1. However for it to work you have to put in a number for example >77. Am I missing something or is it even possible to put a reference cell value B1 for criteria?

 

[JoeMo]

• =AVERAGEIF (range, criteria, [average_range]). My criteria is >B1. However for it to work you have to put in a number for example >77. Am I missing something or is it even possible to put a reference cell value B1 for criteria?

Put the number (not text) 77 in B1 and use criteria: ">"&B1.

 

[dbcooper88]

Put the number (not text) 77 in B1 and use criteria: ">"&B1.

thank you