Nov 26 2020 05:18 AM
Nov 26 2020 05:49 AM
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))))))
Nov 26 2020 06:14 AM - edited Nov 26 2020 06:17 AM
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.