# How to ignore a number well below average

#### dbcooper88

##### New Member
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.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.

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?)

Last edited:
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?

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?

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?

• =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.

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

thank you

Replies
5
Views
99
Replies
6
Views
188
Replies
1
Views
108
Replies
5
Views
480
Replies
8
Views
706

1,203,052
Messages
6,053,225
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back