How to ignore a number well below average

dbcooper88

New Member
Joined
Dec 3, 2016
Messages
36
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,417
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
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:

dbcooper88

New Member
Joined
Dec 3, 2016
Messages
36
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,417
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 3, 2016
Messages
36
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,417
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  • =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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top