Lookup table 2

[pinkpanther56]

I have a table with a formula to add up colums e.g. =SUM(B5:F5) what i would like is to have a formula that will look at the result of that formula and provide a grade.

So if this =SUM(B5:F5) produced a result of 70 a mark of B would be added in cell H5 but 30 would receive an F. I'm having trouble making a lookup that will e.g. provide a C if the mark is less than 70 but greater than 50 but will also provide other marks based on another number range.

Am i making sence?

Appreciate any help.

 

[BlueHorizon]

Hi,
You may be looking at a nested IF function rather than a lookup table to produce different results based on varying criteria.
HTH,

Best,
Blue Horizon

 

[pinkpanther56]

Ok i think i can see what you mean do you think this link might provide the answer?

I haven't used nested IF before.

http://spreadsheets.about.com/od/tipsandfaqs/qt/nested_if.htm

 

[pinkpanther56]

Ok i've tried this formula but it always provides the same result it checks to see if it's greater than 30 and gives an E but if it's more than 50 it doesn't provide a C.

=IF(G4>30,"E",IF(A5>50,"C"))

 

[GlennUK]

Think about your logic in the nested IF ... of course it will give an E for more than 50, as it processes from left to right ( ["IF G4 greater than 30 then result is "E"]occurs before testing for greater than 50 ).

Anyway, I'd use a lookup table, like this:
30 E
40 D
50 C
70 B
90 A
with a VLOOKUP like this:

=VLOOKUP(G4,$A$2:$B$6,2)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pinkpanther56]

Ah ok i'll take a look at your example for future ref thanks Glenn, i got it working in the end with

=IF(G4>=90,"A",IF(G4>=70,"B",IF(G4>=60,"C",IF(G4>=40,"D",IF(G4>=20,"E",IF(G4>=10,"F","U"))))))

Thanks.