Rounding numbers and formulas

[MoonpieCCSD]

I am trying to use numbers rounded to 1 decimal place in formulas with a constant. The formated cell rounds to 1 decimal place but the cell with the formula uses the entire number in the formated cell

Ex. 42.7 is the cell formatted to round to 1 place
42.664237 is the actual number

The constant equation is ((A1-38.3)/38.3)+1 . This cell is formatted to round to 4 places

Answer 1 is :1.1149
Answer 2 is :1.1139

Is there a way to just use the rounded number?

[ChemistB]

Yes, use the ROUND function (there are others you might want to look at too; CEILING, FLOOR, MROUND, ROUNDDOWN, ROUNDUP, INT, TRUNC depending on your rounding criteria). For ROUND, your formula would be

Please Login or Register  to view this content.

Does that work for you?

ChemistB
PS. Excel uses Symetrical Rounding around zero when the rounding digit is a 5.

[MoonpieCCSD]

Thanks ChemistB,

It worked the first try

Moonpie

[daddylonglegs]

ChemistB's suggested formula uses the unrounded value in A1....but rounds the overall result to 1 decimal place. From your description it sounds like you want to use the rounded A1 in the equation. To do that you could use this formula

=(ROUND(A1,1)-38.3)/38.3+1

which can be simplified to

=ROUND(A1,1)/38.3

The result of that won't be rounded at all. You could use another round function to round to 4 decimal places....or just display 4 places