SOLVED

NESTED IF and AND formula help please

Copper Contributor

Hi Excel Gurus 

Its been a while since I worked on nested formulas and need help please. 

 

In cell E7 I want to say if cell E6=a percentage then go off and do xxx

OR if D6=a percentage and e6=TM then go off and do xxxxx

OR if d6=TM and e6=TM then go off and do xxxx2

 

my try is the following which is wrong so its the nested OR I think is the problem????

 

=IFERROR(IFS(((OR(E$6>0,B7*E$6),0),(AND(E$6="TM",$D$6>0),(CALCULATOR!$I18*(CALCULATOR!$M15/CALCULATOR!$K15)),(AND(E$6="TM",$D$6="TM"),(CALCULATOR!$I18*(CALCULATOR!$M15/CALCULATOR!$K15)-D7))))),0)

 

Thanks 

Pam 

2 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Pam Laidler 

Perhaps

 

=IFERROR(IFS(E$6>0,B7*E$6,AND(E$6="TM",$D$6>0),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15,AND(E$6="TM",$D$6="TM"),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15-D7),0)

Thanks Hans that seems to have worked - I shall now go off and edit all the calculators that have already been set up with the different variances where my formula wasn't working. If I come into any problems I'll be back for extra help.
Thanks so much you are amazing.
Pam
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Pam Laidler 

Perhaps

 

=IFERROR(IFS(E$6>0,B7*E$6,AND(E$6="TM",$D$6>0),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15,AND(E$6="TM",$D$6="TM"),CALCULATOR!$I18*CALCULATOR!$M15/CALCULATOR!$K15-D7),0)

View solution in original post