IF OR formula needed

[KClem]

Hi

I need to chart the progress of people between 2 levels of marks but i am not sure how to construct the formula.

Lowest to Highest marks
Level 0 = FS1, FS2, or FS3 (bottom)
Level 1 = 1c, 1b or 1a
Level 2 = 2c, 2b or 2a
Level 3 = 3c, 3b or 3a
Level 4 = 4c, 4b or 4a
Level 5 = 5c, 5b or 5a
Level 6 = 6c, 6b or 6a (top)

For example, I need a true or false outcome if someone at the start of the year had FS2 and got 2c, 2b or 2a at the end of the year (that would be two levels). I need it to look at any mark obtained from Level 0 to Level 3 or higher. I need to know if someone has moved up at least 2 levels.

I need it to calculate two levels from level 0 to level 2, level 1 to level 3, level 2 to level 4 etc. Each letter mark has an equivalent numbered point score which i have included on the spreadsheet if this makes it easier.

i have attached a spreadsheet with the input and am looking for a crack wiz to fill in the blank.

I would be grateful for any help.

Thanks.

[samba_ravi]

h2=IF(IFERROR(LEFT(G2,1)+0,0)-IFERROR(LEFT(F2,1)+0,0)>=2,"Yes","No")

Please Login or Register  to view this content.

Try this and copy towards down

[KClem]

Fantastic, this does the job. Although would it be asking to much for an explanation of what it is doing or could you point me towards something that explains the process. I would like to learn how to apply it to other things if needed.

Thanks