IF Statement Problem

Hi Lee59​,

I am glad that my solution has helped you to solve the problem. You can always reach out to all of us, when you get any query. We all be very happy to help you.

Hi Lee59​,

Your original formula was very close.

 It just needed some commas and parentheses in the right places.

Here’s a working version, plus a few simpler alternatives.

Nested IF

=IF(B2,0,IF(C2,0,IF(D2,0,E2-(E2*F2))))

OR

=IF(OR(B2,C2,D2),0,E2-(E2*F2))

SUM

=IF(SUMPRODUCT(--(B2:D2=TRUE)) + SUMPRODUCT(--(B2:D2="TRUE"))>0,0,E2-(E2*F2))

COUNTIF

=IF(COUNTIF(B2:D2,TRUE) + COUNTIF(B2:D2,"TRUE")>0,0,E2-(E2*F2))

Keep experimenting, Lee every formula you try builds your Excel skills.

You were really close already, and now you’ve got four different ways to solve it!

Hi, After using it I realised it is not returning the correct values and would need to address the second part of the statement. I have added what I thought was right but it does not still work, could you help with this.

Hi Lee59​​,

As I can understand by your formulae, you are trying to use the formulae in below format:

• First IF checks if D23=0 → returns D17-(D17*C24)
• If not, it checks D22=0 → returns D17-(D17*C24)
• If not, it checks D21=0 → returns D17-(D17*C24)
• If none are 0 → returns 0

If my assumption is correct then, there are some issues with the formula which I have tried to explain below:

• You wrote ...C24)IF(D22... → So you are missing a comma between the IF functions.
• Each IF must be closed properly with parentheses.
• Try to update your formulae as given below:

=IF(OR(D21=0,D22=0,D23=0), D17-(D17*C24), 0)

Hi.

Still trying to find my way around this site, The formula always results with "0" The end part is a number being divided by a percentage. That part works on its own. I can be as confusing as a butterflies flight path, if you need any further info please ask.

Lee

Hi.

Thanks for the solution but it does not quite work, This is data for options on ventilation programme and capacity, D21:D23 are 0 if the option to use if the types are not used, If they are "0" then the D17 is the number of people reduced by 30% so the data in D17 is formatted at percentage, hope that makes sense. The end part of the formula works on its own!

Thank you for the time.

Lee

=IF(NOT(OR(D21:D23)),0,D17-(D17*C24))