Rounding off numbers in Excel 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The Excel program does a good job at rounding off numbers and applying
decimal points, however, the residual value is still present when
multiplication or summation is applied. In other words, the cell will show
2.33, but behind the scenes it still has a value of 2.32957, therefore when
multiplying this cell with another, the value used will be 2.32957 and not
2.33. As a result, my Excel totals are different than my Quick Books totals.
Any help will be appreciated. Thank You.
 
Excel is only rounding the appearance and not the value. You will need to
use one of the Round functions.

Try:

=round(a1,2)

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com
 
Forgive me I'm new to this, is that an addition formula you gave me for A1
and A2? Please advise.
 
You're confusing "Rounding" with "Formatting".

The most common format might be $'s.
Format A1 to $ and 2 decimals.
Enter a number in the cell, say 25.7 and the cell displays $25.70

But click in A1 and look in the formula bar.
You see 25.7 *only*.
That's formatting.
And that's obvious, since there is no formula in A1 to mask the value.

Now enter a formula in that same cell (A1) that you formatted.
=B1*B2
And in B1 enter 25.6666
And in B2 enter 1
You get a return of $25.67
That's *also* formatting, but with a formula in A1, you can't see the actual
value in the formula bar.

In C1 enter the formula:
=VALUE(A1)
And you'll see what's in A1, "25.6666".

NOW, revise the formula in A1 to:
=ROUND(B1*B2,2)
And you'll see that C1 changes to match the display in A1, "25.67".
That's *rounding*.

The *actual* value is what you see in C1, "25.67".

Do you understand the difference now?

Also, check out this web page of John McGimpsey on this subject:

http://www.mcgimpsey.com/excel/pennyoff.html
 
Back
Top