SOLVED

formula to only be false for "Manager" if F4 is >0

Occasional Contributor

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)))))))))))

 

 

111222225_0-1648566664031.png

 

6 Replies

@111222225 

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

Hi @Quadruple_Pawn,

 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. 

best response confirmed by 111222225 (Occasional Contributor)
Solution

@111222225 

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

 

 

This is perfect thank you!!
I have one more for you, if your up for it. I need this formula to do the same thing if, if C7="Manager",$F$4>0),FALSE if true:
IF(H7>0%,IF(H7<10%,(E7*0),IF(H7<20%,(E7*1),IF(H7<1000%,(E7*3)))))

@111222225 

You can try the attached file. I've added random data in range H7:H15 to check the formula.