Unable to put multiple IF(AND conditions

[anuradhagrewal]

Hi
This is my excel file

I am trying to put a band automatically in column W but it is not happening
What I need is that if an
Employee salary is >= 15000 but less than 20000 then he/she be in Band "I"
similarly in increments of 5000 till band "XV"
However the above formula is sending an error #VALUE!

[CODE=xls][CODE=xls]
=IF(AND(G5>=15000,G5<=20000),"I",""),IF(AND(G5>=20001,G5<=25000),"II",""),IF(AND(G5>=25001,G5<=30000),"III",""),IF(AND(G5>=30001,G5<=35000),"IV",""),IF(AND(G5>=35001,G5<=40000),"V",""),IF(AND(G5>=40001,G5<=45000),"VI",""),IF(AND(G5>=45001,G5<=50000),"VIII",""),IF(AND(G5>=50001,G5<=55000),"IX",""),IF(AND(G5>=55001,G5<=60000),"X",""),IF(AND(G5>=60001,G5<=65000),"XI",""),IF(AND(G5>=65001,G5<=70000),"XII",""),IF(AND(G5>=70001,G5<=75000),"XIII",""),IF(AND(G5>=75001,G5<=80000),"XIV",""),IF(AND(G5>=80001,G5<=100000),"XV","")

[/CODE][/CODE]

Please help

 

[ajm]

first thing that i noticed is that you are giving both the true condition and the false condition in each of your IF statements. When using an nested if statement, one of the conditions needs to lead into the next if statement. by this i mean,
= if (x =10, "True", if(x=12,"Red",if(and(x=14,y=10),"Green", "")

which reads if X equals ten return TRUE, but if X=12. return "RED", but if X=14 and y = 10, return "Green". if none of these, make it blank ("").

[CODE=xls]
=IF(AND(G5>=15000,G5<=20000),"I",[COLOR=rgb(184, 49, 47)][B]""[/B][/COLOR]),IF(AND(G5>=20001,G5<=25000),"II",[COLOR=rgb(184, 49, 47)][B]""[/B][/COLOR]),IF(AND(G5>=25001,G5<=30000),"III",[COLOR=rgb(184, 49, 47)][B]"")[/B][/COLOR],IF(AND(G5>=30001,G5<=35000),"IV",""),IF(AND(G5>=35001,G5<=40000),"V",""),IF(AND(G5>=40001,G5<=45000),"VI",""),IF(AND(G5>=45001,G5<=50000),"VIII",""),IF(AND(G5>=50001,G5<=55000),"IX",""),IF(AND(G5>=55001,G5<=60000),"X",""),IF(AND(G5>=60001,G5<=65000),"XI",""),IF(AND(G5>=65001,G5<=70000),"XII",""),IF(AND(G5>=70001,G5<=75000),"XIII",""),IF(AND(G5>=75001,G5<=80000),"XIV",""),IF(AND(G5>=80001,G5<=100000),"XV","")

[/CODE]


it would look something like this:

[CODE=xls]
=IF(AND(G5>=15000,G5<=20000),"I",IF(AND(G5>=20001,G5<=25000),"II",IF(AND(G5>=25001,G5<=30000),"III",IF(AND(G5>=30001,G5<=35000),"IV",IF(AND(G5>=35001,G5<=40000),"V",IF(AND(G5>=40001,G5<=45000),"VI",IF(AND(G5>=45001,G5<=50000),"VIII",IF(AND(G5>=50001,G5<=55000),"IX",IF(AND(G5>=55001,G5<=60000),"X",IF(AND(G5>=60001,G5<=65000),"XI",IF(AND(G5>=65001,G5<=70000),"XII",IF(AND(G5>=70001,G5<=75000),"XIII",IF(AND(G5>=75001,G5<=80000),"XIV",IF(AND(G5>=80001,G5<=100000),"XV",""))))))))))))))

[/CODE]

 

[Eric W]

You'd probably be better off with a lookup function, easier to debug than a long nested IF. For example:

Book2
	G	H
5	34567	IV
Sheet9

Cell Formulas
Range	Formula
H5	H5	=LOOKUP(G5,{0,15000,20001,25001,30001,35001,40001,45001,50001,55001,60001,65001,70001,75001,80001,100001},{"","I","II","III","IV","V","VI","VII","VIII","IX","X","XI","XII","XIII","XIV",""})

When parsing out your original formula, I noticed that you were missing VII. It's much easier to spot like this.

 

[DanteAmor]

The "VII" (7) is missing from your formula. So the numbering ends at 90000

Other ways:

varios 05mar2024.xlsm
	G	H	I
4		Option 1	Option 2
5	15000	I	I
6	20000	I	I
7	25000	II	II
8	30000	III	III
9	35000	IV	IV
10	40000	V	V
11	45000	VI	VI
12	50000	VII	VII
13	55000	VIII	VIII
14	60000	IX	IX
15	65000	X	X
16	70000	XI	XI
17	75000	XII	XII
18	80000	XIII	XIII
19	85000	XIV	XIV
20	90000	XV	XV
Hoja1

Cell Formulas
Range	Formula
H5:H20	H5	=IF(G5<15000,"",IF(G5<=20000,"I",IF(G5<=25000,"II",IF(G5<=30000,"III",IF(G5<=35000,"IV",IF(G5<=40000,"V",IF(G5<=45000,"VI",IF(G5<=50000,"VII",IF(G5<=55000,"VIII",IF(G5<=60000,"IX",IF(G5<=65000,"X",IF(G5<=70000,"XI",IF(G5<=75000,"XII",IF(G5<=80000,"XIII",IF(G5<=85000,"XIV",IF(G5<=90000,"XV",""))))))))))))))))
I5:I20	I5	=IF(OR(G5<15000,G5>90000),"",ROMAN(IF(G5=15000,1,INT((G5/5000)-3)+((G5/5000)-3 > INT((G5/5000)-3)))))