So 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"))))))))))
Hello,
I assume you then use the .xls file format. It does have its limitations. The .xls file format only support a limited number of 7 nested functions, while the .xlsx file format supports far more, 64 nested functions. For this formula to work you need to save your file as .xlsx.
If you do save the file as an .xlsx file, then please upload a sample file for us to play with.
Below are some links to more tips and trix about nested IFS.
Excel Nested IF functions explained
When not to use Nested IF functions in Excel
Hi Michal
No matter which version of Excel you are using, with that many levels of IF(), I would suggest you use a VLOOKUP instead.
Sunny
I'm with Sunny here, also for the technical reasons Anders describe.
If you don't know how to use the Vlookup in this case, I made an example.
Good luck!
Frans
