Nested if

[kochan122]

I am new to excel. I am trying to categorise numbers of employees in groups. In one column I have numer of employees and other has nothing in it and I am writing formula like this in the second column, if the number of employees is less that 10, then write 4-9, if less than 50 then 10-49 etc and if any of this then last one 100000-max. Every time I try to use it there is communicate that there is problem with this formula and I don't know where is a problem. From what I looked on internet, this formula should be working.
=IF(G11<10,"4 - 9",
IF(G11<50,"10 - 49",
IF(G11<300,"50 - 299",
IF(G11<500,"300 - 499",
IF(G11<1000,"500 - 999",
IF(G11<5000,"1000 - 4999",
IF(G11<10000,"5000 - 9999",
IF(G11<20000,"10000 - 19999",
IF(G11<50000,"20000 - 49999",
IF(G11<100000,"50000 - 99999",
"100000 - max"))))))))))

 

[James006]

Hi,

Instead of a rather complex formula with Nested Ifs ... you should make your life easier and use VLOOKUP() ...

see https://exceljet.net/tips/how-to-use-vlookup-instead-of-nested-ifs

Hope this will help

 

[Yongle]

or Index and Match

[TABLE="width: 414"]
<colgroup><col></colgroup><tbody>[TR]
[TD] =INDEX($B$1:$B$12,MATCH(G11,$A$1:$A$12,-1))[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

99999999​

[/td][td]

100000 - max​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

99999​

[/td][td]

50000 - 99999​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

49999​

[/td][td]

20000 - 49999​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

19999​

[/td][td]

10000 - 19999​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

9999​

[/td][td]

5000 - 9999​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

4999​

[/td][td]

1000 - 4999​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

999​

[/td][td]

500 - 999​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

499​

[/td][td]

300 - 499​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

299​

[/td][td]

50 - 299​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

49​

[/td][td]

10 - 49​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] FORMULA in H11[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

9​

[/td][td]

4 - 9​

[/td][td][/td][td][/td][td][/td][td][/td][td]

999​

[/td][td]500 - 999[/td][td] =INDEX($B$1:$B$12,MATCH(G11,$A$1:$A$12,-1))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

4​

[/td][td]

0 - 4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[kochan122]

Thank you, it worked!

 

[Yongle]

thanks for your feedback