# Drop lowest numer then average from range

#### plettieri

##### Well-known Member
I would like to dev a formular that would drop the lowest number from a range of numbers, then average the remaining, rounding to next highest whole number. The outer range of each row will be the same but the quantity of data may differ, with some cells having zeros and some blank. Zeros should be included in the average but blanks ignored.

I started with =ROUND(AVERAGEA(B94:AC94),0)

can i drop the lowest number tacking on to this formular? any suggestions?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
On 2002-09-22 07:14, plettieri wrote:
I would like to dev a formular that would drop the lowest number from a range of numbers, then average the remaining, rounding to next highest whole number. The outer range of each row will be the same but the quantity of data may differ, with some cells having zeros and some blank. Zeros should be included in the average but blanks ignored.

I started with =ROUND(AVERAGEA(B94:AC94),0)

can i drop the lowest number tacking on to this formular? any suggestions?

=ROUND((SUM(B94:AC94)-MIN(B94:AC94))/MAX(1,COUNT(B94:AC94)-1),0)

=CEILING(((SUM(B94:AC94)-SMALL(B94:AC94,1))/COUNT(B94:AC94)-1),1)
This message was edited by Albert 1 on 2002-09-22 07:47

Hi,

Try...

=ROUND((SUM(MyRange)-MIN(MyRange))/(COUNT(MyRange)-1),0)

This will only drop one of the minimum numbers. If there are numerous instances of the minimum, try the following array-entered formula (must be entered with Ctrl+Shift+Enter rather than just Enter)

=ROUND(AVERAGE(IF(MyRange<>MIN(MyRange),MyRange)),0)

Excel will enclose the formula in braces, {} if entered properly.

You may want to add some error checking on these.

Thankx for yr quick replies. I am amazed how quickly relpies come back on this board. I am sure it is greatly appreciated by all. It sure is a great spot to come for help.

For future reference for those who look at search for something similar, the sample data that i tested was

100 100 45 45 88 0 0 blank

yielding the answer i wanted to see was 63

When i appiled Aladin and Jay's formulars the same answer was 63 and the one that i need. However, when applying Albert 1's formular it yielded an answer of 53.

I am not sure how this came to be.....
Thankx again

Hi plettirei:

Albert1's formula has a parenthesis missing -- correctly formulation is:

=CEILING((SUM(B4:I4)-SMALL(B4:I4,1))/(COUNT(B4:I4)-1),1)

then this would also yield 63 which is the right answer.

Regards!

Yogi

On 2002-09-22 08:48, plettieri wrote:
Thankx for yr quick replies. I am amazed how quickly relpies come back on this board. I am sure it is greatly appreciated by all. It sure is a great spot to come for help.

For future reference for those who look at search for something similar, the sample data that i tested was

100 100 45 45 88 0 0 blank

yielding the answer i wanted to see was 63

When i appiled Aladin and Jay's formulars the same answer was 63 and the one that i need. However, when applying Albert 1's formular it yielded an answer of 53.

I am not sure how this came to be.....
Thankx again

The formula

[1]

=ROUND((SUM(B94:AC94)-MIN(B94:AC94))/MAX(1,COUNT(B94:AC94)-1),0)

is intended to avoid the #DIV/0! error.

By the way,

SMALL(Range,1)=MIN(Range)

I'd like to point out that Albert has noticed your request for: "rounding to next highest whole number." That's why he used CEILING.

If you want that 21.3 and 21.7 are both rounded to 22, then either replace ROUND in [1] with ROUNDUP or with CEILING. If you choose for the latter, change the last 0 in [1] to 1.
This message was edited by Aladin Akyurek on 2002-09-22 09:34

Thanks Yogi and Aladin for the correction and explanation!

Replies
3
Views
124
Replies
4
Views
670
Replies
5
Views
593
Replies
5
Views
1K
Replies
0
Views
466

1,219,518
Messages
6,148,746
Members
450,832
Latest member
Tyroneb90

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