Hello all. I'm a first time poster and an Excel novice. I apologize if this question has been asked previously, but here goes:
As I understand it, Excel will not allow you to nest more than 7 IF statements in one formula. But, I've also heard that there are ways to circumvent this rule, including the VLOOKUP function, using defined names, and the CONCATENATE function.
I've tried to use these alternatives, but have yet to find one that either applies to my situation or that I can get to work properly. My situation is as follows:
I have created a spreadsheet for a client that lists employees and hire dates. Hire dates have then been assigned values. I'm attempting to create a formula that indicates that if the employee was hired between Aug 1, 1999 and Aug 1, 2002, he or she will be paid at level 1, Aug 2, 2002 to Aug 1 2005, level 2...and so on. So, one column contains the date values and a second contains the formula to assign the levels, 1 - 10. These levels will then end up corresponding to a salary hidden in a cell off to the side of the spreadsheet.
Lets say the hire values run from 1 to 40,000. I believe the formula should read something like the following, with F1 referring to the cell the formula will be placed in:
=IF(AND(F1>1,F1<=4000,1,IF(AND(F1>4000,F1<=8000,2,IF(AND(F1>8000,F1<=12000,3,IF(AND(F1>12000,F1<=16000,4,IF(AND(F1>16000,F1<=20000,5,IF(AND(F1>20000,F1<=24000,6,IF(AND(F1>24000,F1<=28000,7,IF(AND(F1>28000,F1<=32000,8,IF(AND(F1>32000,F1<=36000,9,IF(AND(F1>36000,10))))))))))
As you can see, this becomes very confusing. The FALSE statement becomes the next IF statement, rather than 0, for example. If anyone can make sense of this and can provide any guidance, I would greatly appreciate it. Please post an example of a correct formula in your response.
Thank you very much for your time.
Aaron
As I understand it, Excel will not allow you to nest more than 7 IF statements in one formula. But, I've also heard that there are ways to circumvent this rule, including the VLOOKUP function, using defined names, and the CONCATENATE function.
I've tried to use these alternatives, but have yet to find one that either applies to my situation or that I can get to work properly. My situation is as follows:
I have created a spreadsheet for a client that lists employees and hire dates. Hire dates have then been assigned values. I'm attempting to create a formula that indicates that if the employee was hired between Aug 1, 1999 and Aug 1, 2002, he or she will be paid at level 1, Aug 2, 2002 to Aug 1 2005, level 2...and so on. So, one column contains the date values and a second contains the formula to assign the levels, 1 - 10. These levels will then end up corresponding to a salary hidden in a cell off to the side of the spreadsheet.
Lets say the hire values run from 1 to 40,000. I believe the formula should read something like the following, with F1 referring to the cell the formula will be placed in:
=IF(AND(F1>1,F1<=4000,1,IF(AND(F1>4000,F1<=8000,2,IF(AND(F1>8000,F1<=12000,3,IF(AND(F1>12000,F1<=16000,4,IF(AND(F1>16000,F1<=20000,5,IF(AND(F1>20000,F1<=24000,6,IF(AND(F1>24000,F1<=28000,7,IF(AND(F1>28000,F1<=32000,8,IF(AND(F1>32000,F1<=36000,9,IF(AND(F1>36000,10))))))))))
As you can see, this becomes very confusing. The FALSE statement becomes the next IF statement, rather than 0, for example. If anyone can make sense of this and can provide any guidance, I would greatly appreciate it. Please post an example of a correct formula in your response.
Thank you very much for your time.
Aaron