Is this Excel formula possible?

Associate
Joined
26 Jun 2005
Posts
1,983
Location
Chelmsford
Hi

My girlfriend needs help with a grading system that scales base marks, depending on difficulty. I have provided a link for the grading system in place:

Page 22:

https://qualifications.pearson.com/...ication/Specification_GCSE_L1-L2_in_Music.pdf

So you can see that a raw mark of 10 becomes 13 on standard difficulty but 15 when the piece is more difficult.

Is there a formula (I was thinking an IF statement?) where changing the raw mark would automatically calculate the scaled mark?

Thanks if anyone can help.
 
Soldato
Joined
1 Apr 2014
Posts
18,532
Location
Aberdeen
You want a lookup table. And it's on page 20, not page 22.

Specifically you want VLOOKUP and MS have a video to help you. Copy the whole table into a separate tab on your worksheet and use that. Be sure to give it a sensible name to assist later readability.
 
Associate
Joined
16 Mar 2004
Posts
1,889
Location
Oxford
Without being at a PC, so some elements may be wrong:

=VLOOKUP(table,1,"not found")

Which translates to:

=finds value in(tablenamehere,columnnumberhere,"whattoputifnothingmatches")
 
Soldato
Joined
19 Mar 2012
Posts
6,558
It is a VLOOKUP you need but Jamauk's formula isn't correct.

You need something like =A1 * IFERROR(VLOOKUP(B1,table, 2, 0),1)

A1 is the original score.
B2 is the difficulty of the question.
Table is probably a 2 column table with the difficulty in column 1 and the adjustment to the score in column 2.
I've used an IFERROR as a safety net in case there is a difficulty that doesn't appear in the table, in which case the score won't be adjusted. Not sure you would need to use that if you have a table with all possible difficulties and adjustment factors.
 
Soldato
Joined
19 Mar 2012
Posts
6,558
Ah right, just looked at the table.

You can still use a VLOOKUP, but you need to modify the column based on the difficulty.

=VLOOKUP(A1,score_table,MATCH(B1,score_headers,0),0)

A1 is the raw score.
B1 is the question difficulty.
Score_table is a copy of the table from the document.
Score_header is the 4 cells across the top of the table.
 
Back
Top Bottom