# Average dropping highest and lowest value.

#### srmorgan

##### Board Regular
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Joe4

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

#### srmorgan

##### Board Regular
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

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

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

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

You are welcome.

Replies
3
Views
158
Replies
1
Views
82
Replies
0
Views
158
Replies
1
Views
73
Replies
6
Views
71

1,129,479
Messages
5,636,574
Members
416,925
Latest member
malamutus

### 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