Averaging values, excluding minimum value

[LoriR]

Hello. I am having a problem coming up with a formula to average a set of numbers dropping the lowest of the numbers, then giving equal weight to the remaining 3 numbers. I am a beginning Excel user and cannot figure out how to do this. Example, A1, 95; B1, 80; C1, 70; D1, 60. The value in D1 would be dropped and (95+80+70)/3=81.7 would be the average in cell F1 that I would want calculated by a formula. I just don't seem to be able to figure this out. Any help would be so appreciated! Thank You.

[zbor]

try this:

=AVERAGE(LARGE(A1:A4, ROW($A$1:$A$3)))

comfirmed with ctrl+shift+enter

[LoriR]

I need the formula to drop the lowest value no matter which column the lowest value is located in. It looks like this would just average the first 3 rows. If the lowest value was in A2 I don't think it would work. Is there another way you can think of? Thanks.

[zbor]

Did you try it?

[LoriR]

Did you try it?

I did try it and it came out with the number 95 which is not the average of the 3 highest values. I'm not sure why it came up with that value. Do you have any other suggestion for me to try? It is strange but when I bring up the function arguement box it displays the correct value but what displays in the cell is the largest value in the string of values. Can't figure why it displays something different than what the function arguement box says.

[zbor]

comfirm with ctrl+shift+enter not just enter

[LoriR]

That did work with the ctrl+shift+enter. Can I ask why this is how I had to enter this formula. I have never done that before. Also can you explain how that formula works. What is the LARGE and ROW designating. I have the numbers that I am averaging in rows not in columns. It did not seem to work when I put the numbers in a row. Should it work this way also? The cell references are as listed in my first post. Thank you.

[zbor]

Of course it worked

Beside of regular formula's in excel, those a called array formula: a formulas that don't work with single cells but with array of cells. usually they must be entered with ctrl+shift+enter so that curly brackets {=formula} appear around the formula. You can not enter those brackets manually.

LARGE formula returns the k-th largest value in a data set.

So if you have =LARGE({1,3,5, 7}, 2) it will retun number 5 because it's 2nd largest value of the numbers.

If you put =LARGE({1, 3, 5, 7}, {1, 2}) if you put normal enter it will calculate only with 1st value of the range (7) and that's why you get 95 with normal enter.

If you confirm with ctrl+shift+enter it takes 1st AND 2nd value (7 and 5).

Now, in your example, if you take: =AVERAGE(LARGE(A1:A4, ROW($A$1:$A$3)))

it will take AVERAGE of (ROW(A1:A3) will become 1,2,3) so, AVERAGE of first 3 largest values of the cells A1 to A4

={AVERAGE(LARGE(A1:A4, ROW($A$1:$A$3)))}
={AVERAGE(LARGE({95, 80, 70, 60}, {1, 2, 3}))}
={AVERAGE(95, 80, 70)}
=81.7

[zbor]

If you put numbers in column then ROW function still must remain same because it not taking anything from ROW's it just take 1, 2, 3 etc numbers.

But you must change your data filed from A1:A4 into A1:D1 or so.

[DonkeyOte]

If you know that the array of items required you don't need the Array entry - use an Array constant:

Please Login or Register  to view this content.

where the k array is variable you might revert to an Array

Another non-array alternative here would be:

Please Login or Register  to view this content.

again this assumes 4 numbers at all times

Obviously you can adjust as necessary.

[LoriR]

Another non-array alternative here would be:

Please Login or Register  to view this content.

again this assumes 4 numbers at all times

Obviously you can adjust as necessary.

This second formula is something that I fully understand since I am just a beginner to Excel. I have not worked with arrays yet. Thank you for the simple suggestion. It worked and I don't need an explanation.