How to use IF and ROUND in same formula

[jambog]

I have this formula =IF(((D11-4)^2*C11/16)=0,"",(D11-4)^2*C11/16)

This works for my purpose, but I want the result to round to the nearest whole number.

I have tried variations of =IF(((D11-4)^2*C11/16)=0,"",ROUND(D11-4)^2*C11/16), 0)) bit can't seem to get anything to work. I'm sure I'm missing something simple here.

Of if there would be a better formula to achieve my results without an error in the cell altogether, any help and suggestions would be much appreciated.

Thanks

[Ron Coderre]

Does this work for you?

Please Login or Register  to view this content.

[joeu2004]

Although you could write:

=IF((D11-4)^2*C11/16 = 0, "", ROUND((D11-4)^2*C11/16, 0))

I wonder if that truly gives you what you expect.

On the left, the unrounded expression is exactly zero only when D11=4 or C11=0.

But on the right, the rounded expression could be zero when the unrounded expression is not. For example, if the unrounded expression is 0.004, the rounded expression is zero.

If your intent is not to return zero, you might be happier with:

=IF(ROUND((D11-4)^2*C11/16, 0) = 0, "", ROUND((D11-4)^2*C11/16, 0))

[Phuocam]

Or try:

=IFERROR(1/(1/ ROUND((D11-4)^2*C11/16, 0)),"")