Log In
Register
Home
New Member
February 10, 2019
Offline
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"))))))))))
VIP
Trusted Members
December 7, 2016
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
April 21, 2015
Offline
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
1 Guest(s)
