Rounding and IF/Then Formulas

[Robert V.]

I need to round the result of the following formula to 2 significant figures:

=IF(B21>0,B21/$B$10/($B$11/100),"")

The following formula seems to work well for rounding to 2 sig figs:

=ROUND(C21,2-(1+INT(LOG10(ABS(C21)))))

How can I combine these formulas to yield a nicely rounded result in C21?

Thanks!

 

[Mr_Roscoe]

Robert - you just need to make the rounding the last action in the formula.

so your formula will become:

=ROUND(IF(B21>0,B21/$B$10/($B$11/100),""),2)

trust this helps, Ian R.

 

[Robert V.]

Ian,

Tried this formula, and it still does not round the result to 2 sig figs. Any ideas?

 

[Robert V.]

I think I may have figured out the formula so that it will round the result AND ensure that the cell is blank if there is no number in the adjacent cell:

=IF(B26>0,(ROUND(IF(B26>0,B26/$B$10/($B$11/100)),2-(1+INT(LOG10(ABS(B26/$B$10/($B$11/100))))))),"")

Robert

 

[Mr_Roscoe]

Robert - in comparing the formulas, if you use B10 = 151, B11 = 2, B26 = 50 then your result becomes 17.00. My result is 16.56 as I thought you needed 2 significant figures i.e. 2 decimal places.

Perhaps you require just the integer? If so I woud suggest

=IF(B26="","",IF(B26>0,ROUND(B26/$B$10/($B$11/100),0),""))

If you use B10 = 200, B11 = 2, B26 = 3 then your result becomes 1.00 rather than 0.75

I hope this helps, Ian R.