Excel: Calculating the best 3 grades out of 5

Discussion in 'Mac Apps and Mac App Store' started by batmura, Apr 1, 2018.

  1. batmura macrumors newbie

    Joined:
    Oct 7, 2015
    #1
    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.
     
  2. carp3tshark macrumors newbie

    Joined:
    Jun 11, 2013
    #2
    =AVERAGE(LARGE(B6:F6,{1,2,3}))

    Should work fine to average the top three values in the range B6:F6
     
  3. wirefire macrumors member

    Joined:
    Jun 12, 2015
    #3
    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.
     
  4. batmura thread starter macrumors newbie

    Joined:
    Oct 7, 2015
    #4
    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.
     
  5. Caromsoft macrumors regular

    Caromsoft

    Joined:
    Jun 8, 2012
    #5
    Worked on my Excel 2016 on Mac. I deleted my copy of 2011.
     
  6. NoBoMac macrumors 68000

    Joined:
    Jul 1, 2014
    #6
    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.
     

    Attached Files:

  7. batmura thread starter macrumors newbie

    Joined:
    Oct 7, 2015
    #7
  8. chabig macrumors 603

    Joined:
    Sep 6, 2002
  9. batmura thread starter macrumors newbie

    Joined:
    Oct 7, 2015
    #9
  10. NoBoMac macrumors 68000

    Joined:
    Jul 1, 2014
    #10
    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.
     
  11. chabig macrumors 603

    Joined:
    Sep 6, 2002
    #11
    I bet your numbers in B2:F2 aren’t numbers. They are text. Make sure they are formatted as numbers.
     
  12. DeltaMac macrumors G3

    DeltaMac

    Joined:
    Jul 30, 2003
    Location:
    Delaware
    #12
    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.
     
  13. Caromsoft macrumors regular

    Caromsoft

    Joined:
    Jun 8, 2012
    #13
    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.
     
  14. batmura thread starter macrumors newbie

    Joined:
    Oct 7, 2015
    #14
    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.
     

Share This Page