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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,899
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

Banned user
Joined
Mar 2, 2014
Messages
3,080
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,899
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,899
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,955
Messages
5,834,573
Members
430,297
Latest member
xa_gta

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