Multiple IF (Nested If Statement) returning false as value when false

[MrStrawb]

Good Morning,

I'm new to the forum and trying to brush up on my Excel skills to improve efficiencies with my daily work tasks.

I'm just starting to learn about multiple if / if and statements to save me creating extra columns etc

I'm using a tick box to give a true/false value, if the box is ticked i want it to look at a value in a fixed cell and multiply it by a set price in a cell dependant upon the qty required. (Price is for <=50 and another price for >50.

The formula i have used is:

=IF($C11=TRUE,IF($H$3<=50,F11*$H$3,IF($C11=TRUE,IF($H$3>=50,$G11*$H$3,))))

If the box is ticked and the qty is less than or equal to 50 i want to multiply that under 50 price by the qty, otherwise if the box is ticked and the qty is over 50 i want it to multiply the over 50 price in that cell by the number, otherwise leave the cell blank.

Currently when the box is not ticked i get the value false

I understand i have not put a false value to allow the blank cell if the box isn't ticked but i'm not sure where this would need to go.

Apologies for the long drawn out explanation, I don't have a means of putting a picture up as i don't have dropbox etc at the minute

Any help would be greatly appreciated

Regards, Adam


[TABLE="width: 675"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Welcome to the forum.

Assuming you want a blank result for anything that doesn't match your conditions, you need:

=IF($C11=TRUE,IF($H$3<=50,F11*$H$3,IF($C11=TRUE,IF($H$3>=50,$G11*$H$3,""),"")),"")

 

[MrStrawb]

Hi Rory,

Thanks for such a fast response, it works!

Is there an easy way to learn the order of operations on things like this, e.g. the two brackets with no commas for blank result, which part this relates to

Thanks again for your help

Kind Regards

Adam

 

[RoryA]

It's really just scaling up the basic IF syntax. Each IF function requires 3 parts:
An expression that evaluates as either True or False
What to do if it's True
What to do if it's False

In your original formula, the second IF function was the only one that had all three parts.

 

[Special-K99]

Surely this formula can be rewritten much shorter?

1. There are TWO IF($C11=TRUEs, the second one will only get executed if the first is true therefore we dont need the second one since we know C11=TRUE.
2. The H3 condition is wrong IF(H3<=50.... IF(H3>=50 it cant be equal to 50 in the second condition else the first IF(H3 would have taken precedence.
3. The second IF(H3 is superfluous since if the IF(H3<=50 fails then H3 can only be greater than 50 so no need to check for H3 at all.

This should really be written

IF($C11=TRUE,IF($H$3<=50,F11,G11)*$H$3,"")

 

[RoryA]

I must confess I didn't even look at the formula logic!