Average dropping highest and lowest value.

srmorgan

Board Regular
Joined
Apr 18, 2002
Messages
180
Office Version
  1. 365
Platform
  1. Windows
I am drying to calculate a series of averages of the values in a row omitting the highest and lowest value in the range.
This could be the ten months of a calendar year or four months in a half-year, etc.
I can't seem to work it out.

Any suggestions are appreciated.

thanks to all

I am running Excel 2016 under Windows 10.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Let's say that your data range is B2:M2 (and it may contain blanks).
Try this formula:
Excel Formula:
=(SUM(B2:M2)-MAX(B2:M2)-MIN(B2:M2))/(COUNTIF(B2:M2,"<>")-2)
 
Upvote 0
Solution
Let's say that your data range is B2:M2 (and it may contain blanks).
Try this formula:
Excel Formula:
=(SUM(B2:M2)-MAX(B2:M2)-MIN(B2:M2))/(COUNTIF(B2:M2,"<>")-2)
joe, that worked perfectly, much appreciated. Thank you
 
Upvote 0
You are welcome!

I hope it makes sense what I did.
1. I literally added up all the entries in the list, and then subtracted the biggest (MAX) and the smallest (MIN).
2. I then counted up all the non-blank entries, and subtracted two.
3. I then divided the result from step 1 by the result from step 2 to get the average you want.
 
Upvote 0
You are welcome!

I hope it makes sense what I did.
1. I literally added up all the entries in the list, and then subtracted the biggest (MAX) and the smallest (MIN).
2. I then counted up all the non-blank entries, and subtracted two.
3. I then divided the result from step 1 by the result from step 2 to get the average you want.
joe, I was wondering what the string "<>" did in the function. Is that counting blank spaces?
 
Upvote 0
joe, I was wondering what the string "<>" did in the function. Is that counting blank spaces?
Just the opposite. That is ignoring blank spaces.
Programmically, it is ignoring all cells where the value is not empty (<>"").
So we are counting the non-blank entries and subtracting 2 to get the number or records to divide our addjusted sum by.
 
Upvote 0
Just the opposite. That is ignoring blank spaces.
Programmically, it is ignoring all cells where the value is not empty (<>"").
So we are counting the non-blank entries and subtracting 2 to get the number or records to divide our addjusted sum by.
thanks
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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
Back
Top