Nesting more than 7 ifs...another way?

Please help

I am trying to design a salary checker to work out when an employee started working in a particular year e.g. 2000 and on what scale point eg. MPS1, what year thay will break through onto my threshold scale. The main scale points range from 1 to 6, and an employee would increase a point up the scale yearly. When they reach MPS6 they would stay on that point for two years before progressing to threshold 'upper 1'. There are 3 points on the threshold scale, and 2 years have to be spent on each before progressing to the top of the scale 'Upper 3'. I have managed to set the table up exactly how i want it (attached) but i use nested IF statements to do the calculating and now I have nested 7 statements I cannot continue. Could anyone please show me another way of doing what i want. I dare say by looking at the attached file someone will work out what i am trying to do pretty quickly.

Thanks

Hi

I tried your suggestion and i still cannot make it work, please help!!

the answer to your problem does not need to contain if statements, you could use the match function or the vlookup function to return the value and just add this to C3

regards

Dav

Originally Posted by Blade2304

Please help

I am trying to design a salary checker to work out when an employee started working in a particular year e.g. 2000 and on what scale point eg. MPS1, what year thay will break through onto my threshold scale. The main scale points range from 1 to 6, and an employee would increase a point up the scale yearly. When they reach MPS6 they would stay on that point for two years before progressing to threshold 'upper 1'. There are 3 points on the threshold scale, and 2 years have to be spent on each before progressing to the top of the scale 'Upper 3'. I have managed to set the table up exactly how i want it (attached) but i use nested IF statements to do the calculating and now I have nested 7 statements I cannot continue. Could anyone please show me another way of doing what i want. I dare say by looking at the attached file someone will work out what i am trying to do pretty quickly.

Thanks

-Excel 2007 is the solution. You can get more than 7 nested IF statements.
-or you have to set up a lookup table and use VLOOKUP function

Hello,

I use the following formula many times when I need more than 7 IF arguments. I'm not sure if it works in all situations, though. But worth a try.

E.g., let's say you have the following conditions:

If 1, then X
If 2, then Y
If 3, then Z
Otherwise "Value Not Found"

You'd use this formula, assuming cell A1 is the data input box.

=IF(OR(A1={1,2,3}),CHOOSE("X","Y","Z"),"Value Not Found")

I tried this the other way around, but it didn't work. For example, let's say
the conditions are this:

If X, then 1
If Y, then 2
If Z, then 3

=IF(OR(A1={"X","Y","Z"}),CHOOSE(1,2,3),"Value Not Found")

If anyone knows how to get this formula to work both ways, I'm all ears.

Hope this helps some.