Averaging grades in Excel

  Tycho 20:35 10 May 06
Locked

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

T

  VoG II 20:43 10 May 06

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 06

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.

T

  VoG II 21:19 10 May 06

Example click here

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

  VoG II 21:25 10 May 06

Sorry, corrected formulas click here

  Tycho 22:21 10 May 06

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.

T

  Tycho 18:01 25 Jun 06

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).

T

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

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…