Lookups & formula

[RussChilds]

Hi,
I have a simple lookup that converts a mark into a grade. Here's the forumla:

=LOOKUP(D3,Lookups!$L$10:$M$18)

Here's the lookup table values:

0 U
24 G
36 F
48 E
60 D
72 C
84 B
96 A
108 A*

What I want to do now is work out how far off the next grade they are. So if a pupil scores 62 and gets a D. How can I say to get a C he's 10 more points away in a formula?

Many thanks,
Russ

 

[Robert Mika]

Hi,
I have a simple lookup that converts a mark into a grade. Here's the forumla:

=LOOKUP(D3,Lookups!$L$10:$M$18)

Here's the lookup table values:

0 U
24 G
36 F
48 E
60 D
72 C
84 B
96 A
108 A*

What I want to do now is work out how far off the next grade they are. So if a pupil scores 62 and gets a D. How can I say to get a C he's 10 more points away in a formula?

Many thanks,
Russ

=IF(OR(MAX(L10:L18)=D3,D3=""),"","you are "&INDEX($L$10:$L$18,MATCH(D3,L10:L18)+1)-D3&" points away to get "&INDEX($M$10:$M$18,MATCH(D3,L10:L18)+1))

 

[RussChilds]

Thanks Robert!!! Brilliant!!!

 

[Robert Mika]

Thanks Robert!!! Brilliant!!!

You are welcome