IF function for a value range

  • Thread starter Thread starter girth69
  • Start date Start date
G

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
 
=IF(A1=0,"",IF(A1<10000,"A",IF(A1<20000,"B",IF(A1<30000,"C",IF(A1>=30000,"D","")))))
Regards,
Alan.
 
=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)
 
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?
 
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.
 
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
 
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)
 
Back
Top