Drop lowest numer then average from range

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

Albert 1

Active Member
Joined
Feb 22, 2002
Messages
393
=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
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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.
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Albert 1

Active Member
Joined
Feb 22, 2002
Messages
393
Indeed, my answer wasn't correct.
Thanks Yogi and Aladin for the correction and explanation!
 

Forum statistics

Threads
1,144,210
Messages
5,723,047
Members
422,476
Latest member
beck85

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