Average dropping highest and lowest value.

srmorgan

Board Regular
Joined
Apr 18, 2002
Messages
179
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

srmorgan

Board Regular
Joined
Apr 18, 2002
Messages
179
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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.
 

srmorgan

Board Regular
Joined
Apr 18, 2002
Messages
179

ADVERTISEMENT

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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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.
 

srmorgan

Board Regular
Joined
Apr 18, 2002
Messages
179
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,490
Messages
5,636,628
Members
416,932
Latest member
mm07

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