# Excel help with marking spreadsheet

Discussion in 'Assessment' started by NoNoNoah, Sep 28, 2011.

1. ### NoNoNoahNew commenter

Any Excel 2010 experts out there who can help me set up formulae that would convert test marks to equivalent values? e.g. a mark between 32-39 shows as 3B, 40-47 shows as 3A etc.
I realise I will need to input the values but not sure where to start.
Thanks

2. ### FGTO

I'm not the greatest expert in Excel, but try this.
This example assumes that the mark is entered in cell A2. I doesn't matter as long as the formula is referencing the cell where the mark is.
=IF(AND(A2>=32,A2<39),"3b",IF(AND(A2>=40,A2<47),"3a"))
The bit in bold is the part you'll need to copy and paste between the external brackets, and you'll need to an external bracket for each condition you've added. (I think you can have up to 7 in a nested if function like this.)
E.g. If I were to add another condition for 4c it would be
=IF(AND(A2>=32,A2<39),"3b",IF(AND(A2>=40,A2<47),"3a",IF(AND(A2>=48,A2<55),"4c")))
(NB - I made the marks 48 and 55 up for the last one.)
Hope that makes sense.

3. ### Bjojo

Have you investigated vlookup?