showing a rounded number without rounding it

  • Thread starter Thread starter svetlana
  • Start date Start date
S

svetlana

how do i show a rounded number without rounding it
for example:
i need to show a number $754,612 as $754,600, although i
have to use $754,612 in the calculation.

maybe there is a way to program a custom format, but i
know know how.

thank you!
svetlana
 
svetlana said:
how do i show a rounded number without rounding it
for example:
i need to show a number $754,612 as $754,600, although i
have to use $754,612 in the calculation.

maybe there is a way to program a custom format, but i
know know how.

thank you!
svetlana
Click to expand...
The only way I know to do this with a custom format can only handle
rounding the display at the thousands place (or millions and billions).

The custom format to do thousands is:
#,##0,

Someone may prove me wrong, but I don't think custom formats can
handle rounding for hundreds.


Hope that helps,

Matthew
 
Svetlana,

I set up a custom function in an Excel VBA module (see
below):

Function SpecialRound(xValue As Double)
'rounds numbers to a certain number of places, based
on the number size
Dim n As Double
Dim size As Integer
n = xValue
size = Len(Str(n)) - 1

Select Case size
Case Is = 3
SpecialRound = Int((n / 10) + 0.5) * 10
Case Is = 4
SpecialRound = Int((n / 100) + 0.5) * 100
Case Is = 5
SpecialRound = Int((n / 100) + 0.5) * 100
Case Is = 6
SpecialRound = Int((n / 100) + 0.5) * 100
Case Is = 7
SpecialRound = Int((n / 1000) + 0.5) * 1000
Case Is = 8
SpecialRound = Int((n / 100) + 0.5) * 100
End Select

End Function

This function gets a number passed to it from a cell of
the worksheet. In the cell, enter the
function "=SpecialRound(cell)", where "cell" is the cell
of the number you wish to display (i.e. A3, B17). It's
not clear just how you want to format your values (i.e.
show only numbers to the nearest 100 dollars, to the
nearest 1,000 dollars, etc.). If you change the "100"
values in each of "SpecialRound = Int(n / 100) * 100"
formulas, you can change the number of places the
original value will be rounded off to (i.e. 100 will
round to hundreds, 1000 will round to thousands, etc.).
The "+ .5" causes the numbers to be rounded up if they
are above 50, and to be rounded down if they are below 50.

Andrew Lenczycki
 
Back
Top