Averaging grades in Excel

  Tycho 20:35 10 May 2006

I would like a function that will calculate the average of a set of examination grades where the grades are stated as strings: A*, A, B, C, D, E, F, G.

For example if a student does three test and gets A*, A, B the average would come out as B.

Has anyone written a VBA function for this or will I have to roll up my sleaves and try for myself?

In hopes


  VoG II 20:43 10 May 2006

Why would the average come out as B?

Can't you substitute numbers for the grades, use the AVERAGE function, then translate back to grades. You could use lookup tables to do this.

  Tycho 20:47 10 May 2006

Sorry. It wouldn't come out as B. It would come out as A.

I was thinking of the lookup table route but a custom functuion would mean that a less skilled user could build her own spreadsheets without having to add the tables.


  VoG II 21:19 10 May 2006

Example click here

Cells in blue indicate the formula in the cell immediately to the left.

  VoG II 21:25 10 May 2006

Sorry, corrected formulas click here

  Tycho 22:21 10 May 2006

That's looking quite neat, thanks. I will now see how I can fit that into the existing format. Should be possible.

I will leave this thread open for a bit to see if anyone comes up with any other ideas to fire the little grey cells.


  Tycho 18:01 25 Jun 2006

In the end I decided to roll mhy sleaves up and write a little VBA function which did the job. You call it by entering into the cell =GRADEAVERAGE(<BLOCK>). Let me know if you are interested in recieving a copy (free, of course).


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

See iconic duo Smith and Foulkes' epic animation for the BBC's Winter Olympics coverage

iMac Pro review

Idées cadeaux pour geeks et tech addicts