Formula to either calculate or 0 based on IF

[Willjcb]

Hello,

I've tried searching for this, but I can't find anything that matches my req. Apologies if I haven't looked hard enough but I'm on the verge of walking out into the ocean.

I want to replicate the following as a formula:

IF (B43 > 0) {
B43*0.2
}

I've tried a bunch of SUMIF and IF statements, but I can't figure it out..

Please help because I'm now waist-deep in water.

[AlKey]

Try this

Please Login or Register  to view this content.

[Willjcb]

You are an absolute bloody hero. I'm going to gloss over how simple that was and how stupid I am.

Thanks AlKey!

[jason.b75]

Can the value of B43 ever be negative? If not then you don't even need the logical test.

=B43*0.2 will suffice, 0*0.2 is still 0

If negatives are possible in B43, then you could use MAX.

=MAX(0,B43*0.2)

No advantage, or disadvantages over AlKey's suggestion, just showing you some alternative methods.

[Willjcb]

Hi Jason,

It's being used to calculate a tax amount as part of a cash flow forecast, so if the gross profit in any given month is negative, I want the cell to print 0, since there is no taxable value.

What I ideally want is: IF (B43 >= 0) THEN (B43*0.2) ELSE 0

[Tony Valko]

What I ideally want is: IF (B43 >= 0) THEN (B43*0.2) ELSE 0

Like this...

=IF(B43>=0,B43*0.2,0)

[jason.b75]

Hi Jason,

It's being used to calculate a tax amount as part of a cash flow forecast, so if the gross profit in any given month is negative, I want the cell to print 0, since there is no taxable value.

What I ideally want is: IF (B43 >= 0) THEN (B43*0.2) ELSE 0

In that case, either AlKey's suggestion, or MAX will work fine, with AlKey's method you can test for >=0 or >0 they will both return the same result.