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 h...
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.