Multiple IF Function in EXCEL 2013

[czhbpr]

Afternoon everyone, Been working on this now for about half a day and can not work out why it is not working.

The Plan was if F9 is equal to Alpha and W9 is between P1 and Q1 then subtract Z3 off X9 if F9 was not equal to Alpha and is between T1 and U1 then subtract Z4 from X9

=IF(F9="ALPHA",IF(AND($W9>=$P$1,$W9<=$Q$1),$X9-($X9*$Z$3/100),"") IF($W9>=$T$1,$W9<=$U$1),$X9-($X9*$Z$4/100))

How ever i get a #VALUE! Error on anything with a ALPHA in F9

Help would be much appreciated

 

[czhbpr]

I should add it should leave the cell Blank if it does not fit with in the rules. Currently it seems to be doing everything as per blank rule even if it is not between the T and U Fields.

 

[Nishant94]

Use this:

=IF(F9="ALPHA",IF(AND($W9>=$P$1,$W9<=$Q$1),$X9-($X9*$Z$3/100),IF(AND($W9>=$T$1,$W9<=$U$1),$X9-($X9*$Z$4/100),"")))

 

[mikerickson]

Try

=IF(AND(F9="Alpha", $P$1<=$W9, $W9<=$Q$1), X9-Z3, x9-Z4)

 

[FlameRetired]

I interpreted a bit differently.

=IF(AND(F9="Alpha",W9 > P1,W9 < Q1),X9-Z3,IF(AND(W9 > T1,W9 < U1),X9-Z4))

 

[czhbpr]

Use this:

=IF(F9="ALPHA",IF(AND($W9>=$P$1,$W9<=$Q$1),$X9-($X9*$Z$3/100),IF(AND($W9>=$T$1,$W9<=$U$1),$X9-($X9*$Z$4/100),"")))

This seems to have fixed it if it was equal to Alpha, but now if it is not equal to ALpha it will put up a False result. it will not go by the 2nd option of if F9 was not equal to Alpha and is between T1 and U1 then subtract Z4 from X9


Thank you, so far it is much appreciated

 

[czhbpr]

I interpreted a bit differently.

=IF(AND(F9="Alpha",W9 > P1,W9 < Q1),X9-Z3,IF(AND(W9 > T1,W9 < U1),X9-Z4))

This one seems to be throwing up odd results, and as the other one is does not have the 2nd part if it is not equal to ALpha use different criteria.

 

[Nishant94]

This seems to have fixed it if it was equal to Alpha, but now if it is not equal to ALpha it will put up a False result. it will not go by the 2nd option of if F9 was not equal to Alpha and is between T1 and U1 then subtract Z4 from X9


Thank you, so far it is much appreciated

Use this:

=IF(F9="ALPHA",IF(AND($W9>=$P$1,$W9<=$Q$1),$X9-($X9*$Z$3/100),""),IF(AND($W9>=$T$1,$W9<=$U$1),$X9-($X9*$Z$4/100),""))

 

[czhbpr]

Use this:

=IF(F9="ALPHA",IF(AND($W9>=$P$1,$W9<=$Q$1),$X9-($X9*$Z$3/100),""),IF(AND($W9>=$T$1,$W9<=$U$1),$X9-($X9*$Z$4/100),""))

Worked, Thank you