Mar 29 2022 08:16 AM
Hello, I need some help.
I need to change this formula to only be false for "Manager" if F4 is >0
=IF($F$4>0,FALSE,IF(C7="Manager",IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1.5),IF($J$4<15%,(E7*2),IF($J$4<20%,(E7*2.5),IF($J$4<100%,(E7*3))))))),IF(C7="Assistant Manager",IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1),IF($J$4<15%,(E7*1.5),IF($J$4<20%,(E7*2),IF($J$4<100%,(E7*2.5))))))),IF(OR(C7="Sales Advisor",C7="Lead Advisor"),IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*0.5),IF($J$4<15%,(E7*1),IF($J$4<20%,(E7*1.5),IF($J$4<100%,(E7*2)))))))))))
Mar 29 2022 08:47 AM
=IF(AND(C7="Manager",$F$4>0),FALSE,IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*E7),IF($J$4<15%,(E7*2),IF($J$4<20%,(E7*2.5),IF($J$4<100%,(E7*3)))))),IF(C7="Assistant Manager",IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1),IF($J$4<15%,(E7*1.5),IF($J$4<20%,(E7*2),IF($J$4<100%,(E7*2.5))))))),IF(OR(C7="Sales Advisor",C7="Lead Advisor"),IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*0.5),IF($J$4<15%,(E7*1),IF($J$4<20%,(E7*1.5),IF($J$4<100%,(E7*2)))))))))))
Is this what you are looking for? A value for "Manager" is only calculated if F4<=0.
Mar 29 2022 12:46 PM
Hi @OliverScheurich,
I just tested and it seems to override the formula and give the sales advisors and assistant manager, the manager equation of =IF(AND(C7="Manager",$F$4>0),FALSE,IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*E7),IF($J$4<15%,(E7*2),IF($J$4<20%,(E7*2.5),IF($J$4<100%,(E7*3)))))),
and not these formulas
IF(C7="Assistant Manager",IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1),IF($J$4<15%,(E7*1.5),IF($J$4<20%,(E7*2),IF($J$4<100%,(E7*2.5))))))),IF(OR(C7="Sales Advisor",C7="Lead Advisor"),IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*0.5),IF($J$4<15%,(E7*1),IF($J$4<20%,(E7*1.5),IF($J$4<100%,(E7*2)))))))))))
It does make the manager false as needed, but not the right equation.
Mar 29 2022 01:21 PM
Solution=IF(AND(C7="Manager",$F$4>0),FALSE,IF(AND(C7="Manager",$J$4>0%),IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1.5),IF($J$4<15%,(E7*2),IF($J$4<20%,(E7*2.5),IF($J$4<100%,(E7*3)))))),IF(C7="Assistant Manager",IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1),IF($J$4<15%,(E7*1.5),IF($J$4<20%,(E7*2),IF($J$4<100%,(E7*2.5))))))),IF(OR(C7="Sales Advisor",C7="Lead Advisor"),IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*0.5),IF($J$4<15%,(E7*1),IF($J$4<20%,(E7*1.5),IF($J$4<100%,(E7*2)))))))))))
Sorry for the first suggestion which was wrong. You can try the attached file with the formula above.
Mar 29 2022 01:59 PM
Mar 29 2022 03:11 PM
Mar 29 2022 03:48 PM
You can try the attached file. I've added random data in range H7:H15 to check the formula.
Mar 29 2022 01:21 PM
Solution=IF(AND(C7="Manager",$F$4>0),FALSE,IF(AND(C7="Manager",$J$4>0%),IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1.5),IF($J$4<15%,(E7*2),IF($J$4<20%,(E7*2.5),IF($J$4<100%,(E7*3)))))),IF(C7="Assistant Manager",IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*1),IF($J$4<15%,(E7*1.5),IF($J$4<20%,(E7*2),IF($J$4<100%,(E7*2.5))))))),IF(OR(C7="Sales Advisor",C7="Lead Advisor"),IF($J$4>0%,IF($J$4<5%,(E7*0),IF($J$4<10%,(E7*0.5),IF($J$4<15%,(E7*1),IF($J$4<20%,(E7*1.5),IF($J$4<100%,(E7*2)))))))))))
Sorry for the first suggestion which was wrong. You can try the attached file with the formula above.