Hi everyone,
I am a teacher and I give letter marks including + and - from E- up to A+. What I'd like is for excel to:
Assign a number to each letter grade mark (I have done a table on a separate page in the spreadsheet to reflect this)
Calculate the average numerical grade based on this information.
Convert that number back into an overall letter grade.
For example: Johnny gets: A, B+, A-, C+, B
So he gets numerically: 13, 11, 12, 8, 10.
The average is 10.8 (rounded to 11) So a B+ overall.
Your profile say MS-Off version 10. Is that 2010? Or does it refer to Windows 10? Do you have Excel 365?
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Welcome to the forum.
For 365 - in B23 copied across:
=XLOOKUP(ROUND(AVERAGE(IFNA(BYROW(B$12:B$21,LAMBDA(r,XLOOKUP(r,'Score Sheet'!$A$2:$A$16,'Score Sheet'!$B$2:$B$16))),"")),0),'Score Sheet'!$B$2:$B$16,'Score Sheet'!$A$2:$A$16)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
If you want the total numerical values in B23, you could use this array* formula:
=SUM(IFERROR(MATCH(B3:B21,'Score Sheet'!$A$3:$A$16,0),0))
and copy across to P23.
*Note that if you are using XL2019 or earlier, you need to commit the formula using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.
If you want the average of the equivalent scores, then you can use this instead in B23:
=AVERAGE(IFERROR(MATCH(B3:B21,'Score Sheet'!$A$3:$A$16,0),""))
Again, you will need to use CSE to commit the formula if you are using Excel versions up to 2019, then copy across.
If you just want the equivalent letter grade for that average score, then you can use this formula in B23:
=IFERROR(INDEX('Score Sheet'!$A$2:$A$16,MATCH(AVERAGE(IFERROR(MATCH(B3:B21,'Score Sheet'!$A$3:$A$16,0),"")),'Score Sheet'!$B$2:$B$16)),"")
Use CSE to commit if necessary, then copy across.
Hope this helps.
Pete
Pl see file. ARRAY formula in J3 copied down.
Formula:
Please Login or Register to view this content.
Another example in M3 copied across
Formula:Please Login or Register to view this content.
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
Last edited by kvsrinivasamurthy; 06-12-2024 at 10:51 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Here is another option if you do have Excel 365, no need to drag/ copy across:
=LET(g,'Score Sheet'!A2:A16,s,'Score Sheet'!$B$2:$B$16,XLOOKUP(ROUND(BYCOL(B3:O21,LAMBDA(x,AVERAGE(XLOOKUP(x,g,s,"")))),0),s,g))
If things don't change they stay the same
Hi Pete_UK
Thank you for your help, this worked great! I was wondering if you'd mind breaking it down into layman's terms for each step, as I have multiple documents I'd like to apply this to, and some have different pages where the score sheet is included. Currently I've tried to do this in a different document, but because the score sheet is on a different sheet not titled 'score sheet' (although everything else is the same) it's not finding it. If I know how to include that in the code I'll be good to go
Last edited by specndim; 06-12-2024 at 10:44 PM.
Thank you for your help everyone.
I don't think it's 365 that I'm using. Pretty sure it's an earlier version.
If you wouldn't mind going the extra step and breaking down the code a bit so I can apply them across multiple documents I'd really appreciate it. Sometimes I use multiple sheets per document and the score sheet is not always on the same page or in the same place. So if you could show me which part of the code selects the score sheet, etc then that would be awesome! Thanks so much
Which version? Does my formula work? If so, then you have 365. If not, you need to look under File > Account in Excel and update your forum profile. This is important.I don't think it's 365 that I'm using. Pretty sure it's an earlier version.
They are formulae and there are several - which one do you want explaining?If you wouldn't mind going the extra step and breaking down the code a bit
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks