Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
showing a rounded number without rounding it
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
showing a rounded number without rounding it
svetlana wrote:
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
showing a rounded number without rounding it
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 -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a Rounded number in a LOOKUP command | Excel Discussion (Misc queries) | |||
how to display rounded number in a cell w/formula | Excel Discussion (Misc queries) | |||
Number are rounded to thousand, but they now do not foot | Excel Discussion (Misc queries) | |||
Can rounded numbers be summed without rounding errors? | Excel Discussion (Misc queries) | |||
marrying a rounded number to concatenation. | New Users to Excel |