Drop lowest numer then average from range

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,558
Platform
  1. MacOS
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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)
 
Upvote 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Indeed, my answer wasn't correct.
Thanks Yogi and Aladin for the correction and explanation!
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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