Forum Discussion
If Formula
Hi everyone,
I’m trying to creat a formula that calculate severance reward for employees based on the years of service and salary. This reward not for all staff only for people with low wages and have completed a certain years of service.
For example in the employee salary is less than 2k and has completed 5 years he will get 3 months salary as a bonus if 10 years will get 4 months bonus ... etc. I came up with formula but is is only working if I use one segment, how can I make excel calculate the whole formula using if functions
=IF(AND(K13>=5,K13<10,D8<2000),D8*3,0),IF(AND(K13>10,K13<15,D8<2000),D8*4,0),IF(AND(K13>=15,D8<2000),D8*6,0),IF(AND(K13>=5,K13<10,D8<2000,D8<5000),D8*2,0),IF(AND(K13>10,K13<15,D8<2000,D8<5000),D8*3,0),IF(AND(K13>15,D8<2000,D8<5000),D8*4,0)
Sorry for the long message:)
I’m trying to creat a formula that calculate severance reward for employees based on the years of service and salary. This reward not for all staff only for people with low wages and have completed a certain years of service.
For example in the employee salary is less than 2k and has completed 5 years he will get 3 months salary as a bonus if 10 years will get 4 months bonus ... etc. I came up with formula but is is only working if I use one segment, how can I make excel calculate the whole formula using if functions
=IF(AND(K13>=5,K13<10,D8<2000),D8*3,0),IF(AND(K13>10,K13<15,D8<2000),D8*4,0),IF(AND(K13>=15,D8<2000),D8*6,0),IF(AND(K13>=5,K13<10,D8<2000,D8<5000),D8*2,0),IF(AND(K13>10,K13<15,D8<2000,D8<5000),D8*3,0),IF(AND(K13>15,D8<2000,D8<5000),D8*4,0)
Sorry for the long message:)
2 Replies
Hi TurkiAlhamed,
One of the problem with your formula is that you set the "else" portion of all your "IF" to "0". Instead, the "else" portion should be the next "IF" (hopefully it make sense)
=IF(AND(K13>=5,K13<10,D8<2000),D8*3,IF(AND(K13>10,K13<15,D8<2000),D8*4,IF(AND(K13>=15,D8<2000),D8*6,IF(AND(K13>=5,K13<10,D8<2000,D8<5000),D8*2,IF(AND(K13>10,K13<15,D8<2000,D8<5000),D8*3,IF(AND(K13>15,D8<2000,D8<5000),D8*4,0))))))That said, the last 3 "IF/AND" are evaluating cell D18 to be lower then 2000 and lower then 5000. If D18 is lower then 2000, consequently it is lower then 5000 so you don't need to check the lower then 2000. Here's a revised version of your formula with this adjustment...
=IF(AND(K13>=5,K13<10,D8<2000),D8*3,IF(AND(K13>10,K13<15,D8<2000),D8*4,IF(AND(K13>=15,D8<2000),D8*6,IF(AND(K13>=5,K13<10,D8<5000),D8*2,IF(AND(K13>10,K13<15,D8<5000),D8*3,IF(AND(K13>15,D8<5000),D8*4,0))))))Hi
Thank you for your reply and help, i just tried it and it is working, as for the last 3 ifs, it is supposed to be >2000 and <5000, and I updated that as well.
Very grateful for you help.
