IF function for a value range

[girth69]

Hi,

I need some help creating an IF function. I have a list of salary
brackets e.g A 0 - 10,000
B 10,000 - 20,000
C 20,000 - 30,000
D 30,000 - 40,000
etc

On a seperate spreadsheet i have different cells with peoples salaries
in. In a seperate cell underneath their salary cells, i have an empty
cell that needs to have the relevant letter in that shows which band
they fall into A,B,C,D etc.

What i need is an IF function in the blank cell that will automatically
come up when a persons salary is typed in.

E.g : - if i type in 24,000 into the salary cell, i want the blank cell
to then come up with the letter C.

Thanks

 

[Alan]

=IF(A1=0,"",IF(A1<10000,"A",IF(A1<20000,"B",IF(A1<30000,"C",IF(A1>=30000,"D","")))))
Regards,
Alan.

 

[Bob Phillips]

=VLOOKUP(A1,{0,"A";10000,"B";20000,"C";30000,"D"},2)

extend as required


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[girth69]

Thanks,

But the problem i am facing is that i have a list of 13 different
catergories. when i have typed in 8 catergories into the if function as
stated in this post, it wont let me add any more. it comes up saying
theres errors all the time. is there a limit to the number of IFs you
can have in one formula?

 

[Alan]

Yes there is, the maximum number of IF's is seven.
Use Bob's formula, that will take as many as you like,
Regards,
Alan.

 

[girth69]

if i type the complete list of catergories could you please give me the
required formula. Thanks,

exceeding 70,000 A
65,000 – 70,000 B
60,000 – 65,000 C
55,000 – 60,000 D
50,000 - 55,000 E
45,000 – 50,000 F
40,000 – 45,000 G
35,000 – 40,000 H
30,000 – 35,000 I
25,000 – 30,000 J
20,000 – 25,000 K
15,000 – 20,000 L
less than 15,000 M

 

[Bob Phillips]

it helps if you give us the correct facts to start.

=VLOOKUP(A1,{0,"M";15000,"L";20000,"K";30000,"J"},2)

then

=VLOOKUP(A1,{0,"M";15000,"L";20000,"K";30000,"J";35000,"I"},2)

then

=VLOOKUP(A1,{0,"M";15000,"L";20000,"K";30000,"J";35000,"I";40000,"H"},2)

you should be able to figure out the final solution

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)