Excel: Calculating the best 3 grades out of 5

[batmura]

Hello,

I'm using Excel for Mac 2011.

I would like to calculate the average of the best 3 grades out of 5.

I tried =AVERAGE(LARGE(B6:F6,{1,2,3})) without success. Excel keeps telling me that the formula I typed contains an error.

What am I doing wrong?

Any help would be much appreciated.

 

[carp3tshark]

=AVERAGE(LARGE(B6:F6,{1,2,3}))

Should work fine to average the top three values in the range B6:F6

 

[wirefire]

1 catch, B6 thru F6 must contain all numeric values. (technically speaking 3 of the 5 columns must contain numeric values to satisfy the 3 largest numbers in the row). if there are 3 or more non-numeric columns excel will tell you there is an error.

 

[batmura]

I'm just asking one more time hoping that some others may see this.

Can anyone using a MAC computer verify whether the formula above works? I have now tried this one 3 different machines and always get an error message.

 

[Caromsoft]

Worked on my Excel 2016 on Mac. I deleted my copy of 2011.

 

[NoBoMac]

It works for me.

Per what wirefire wrote above, need three non-blank cells for this to work, otherwise, you get the error. See attached (working and less than three non-blank).

ADD: and this is Office 2011.

 

[batmura]

For the love of God, someone tell me why it's not working. Here's a ss: https://ibb.co/mqTC6x

 

[chabig]

What’s that “#2” part?

 

[batmura]

I don't know. It pops up the second I enter the formula:
=AVERAGE(LARGE(B6:F6,{1,2,3}))
[doublepost=1524339711][/doublepost]And this is the error message I keep getting both on my MacBook Air and MacBook Pro: https://ibb.co/dPrhOc

 

[NoBoMac]

Who knows, without the actual spreadsheet.

Might be a macro on the sheet that is messing things up. Personally, have never setup/removed macros, so, that is as far as my guessing goes.

Would be interesting to see what the correction wizard does: might provide a clue or two.

 

[chabig]

I don't know. It pops up the second I enter the formula:
=AVERAGE(LARGE(B6:F6,{1,2,3}))
[doublepost=1524339711][/doublepost]And this is the error message I keep getting both on my MacBook Air and MacBook Pro: https://ibb.co/dPrhOc

I bet your numbers in B2:F2 aren’t numbers. They are text. Make sure they are formatted as numbers.

 

[DeltaMac]

What happens when you remove the "#2" from the formula block?

I agree that the cells you are using are probably not set for number format, but for text (so even if you enter numbers, Excel considers them text, and numerical formulae won't work on text). I think you have to make sure the cells are formatted for numbers only.

 

[Caromsoft]

I tried it again using cells formatted as text, numbers, and general. With each the formula worked correctly. I also got the #2 when I copied and pasted the formula from post #2 above, but not the one in post #9.

 

[batmura]

They are definitely not formatted as text because when I do a simple sum or average it works fine. My problem is with it picking the best 3 out of 5.