Nesting Formulas

[honkin]

hi

I am struggling to put together a number of formulas to get the required result I am after.

I started off using the IF function and then tried the IFS function, but have not been able to put them together. Each of them works individually, but I have not been able to get the correct syntax to combine them.

Here are the 3 individually:-

Formula 1

=IF(AND(D2<=3,U2=1),95*S2-95,-100)

Formula 2

=IF(AND(D2>=4,D2<=7,U2<=2),95*S2-95,-100)

Formula 3

=IF(AND(D2>=8,U2<=3),95*S2-95,-100)

In English, IF formula 1 OR formula 2 or formula 3 are TRUE, perform the following calculation - 95*S2-95, if FALSE the result is -100

I tried this at the finally and it unfortunately gave all only positive results when U2 was showing as 1, so is obviously not working correctly either

=IF(AND(OR(D2<=3,U2=1),OR(D2>=4,D2<=7,U2<=2),OR(D2>=4,D2>=8,U2<=3)),95*S2-95,-100)

Using IFS I was unable to get it working at all.

Any suggestions of the correct syntax to have the 3 working together?

Thanks in advance

 

[Finalfight40]

Hi Honkin

I think you had the right idea but just the wrong order with the OR AND, try:

=IF(OR(AND(D2<=3,U2=1), AND(D2>=4,D2<=7,U2<=2), AND(D2>=8,U2<=3)), 95*S2-95,-100)

 

[honkin]

ha!

Thanks so much for the reply, Finalfight40. Ah to be so close.

Thanks again. it works a treat

 

[Finalfight40]

You're Welcome.

Glad it works for you.