Excel Formula problem

[DejaVu]

I'm usually quite good with Excel and can, most of the time, get by. But I've discovered his maybe a little bit too advanced for me...

This is the problem in laymans terms as I think it would be the easiest way to explain it.

I'm trying to find the values to put into the K Column which is known as 'Grade'. Grades are A thru to F with X being before A... ie X, A, B, C, D, E, F.

Now Column I is a number entered, as is column B.

An example is

I = Achieved - 1,400
B = Hours - 25

I want to divide the 'Achieved' by the 'Hours' (Easy, =SUM(I2/B2) )

But what I want is to work out from the answer to put in K is something like this.

If the answer is

0-300 = X
301-500 = A
501-800 = B
801-1100 = C
1101-1300 = D
1301 - 1501 = E
1500+ = F

What formula would I need to put into K. I realise I will need to put other cells in to do the workings etc, but my head cannot get round it to well. I think this is where => or <= comes in (More than or less than), but have been doing my head in about it.

I realise this doesnt explain it too well (but I've attached the XLSM) to show what I mean...

[prezzy]

I would use the IF function to to do this - if I get time later / tomorrow I will do for you.

[ka$h]

Using Nested IF Statements you would get:

=IF(AND(I2>=0,I2<=300),"X",if(and(I2=>301,I2<=500),"A",if(and(I2=>501,I2<=800),"B",if(and(I2=>801,I2<=1100),"C",if(and(I2=>1101,I2<=1300),"D",if(and(I2=>1301,I2<=1500),"E",if(I2=>1501,"F","INVALID")))))))

If the number is less than 0, then the result would read invalid

K

[inspectercoley]

Yeah as Prezzy says:

=IF(J2<=300,"X",IF(J2<=500,"A",IF(ETC,"B"," "))) etc

[DejaVu]

ka$h... You are an absolute diamond. Thank you soooo much.

K+ +Dollars!

[Mr.James]

You need to use a lookup table...

I've attached an example using Jade A's Sheet.