MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Anthony on February 04, 2002 8:43 AM

Hello to all!!! I would like to convert $1,150 to $1 without losing the true valus of the original amount. The same applies to a number like $1,550,600....I would like to display $1,551. Is this possible? Any help would be greatly appreciated.


Posted by Brian on February 04, 2002 9:02 AM

I think I have you right on this. Basically, you want thousands displayed as signle units?

To do this, select the cells you want to format, and from the menus select "Format.cells".
Next select the custom category.
Enter 0, in the code box (you must include the comma)
Put a currency symbol before the zero if you want to maintain that and click "OK".
All thousands will now be represented as single values.

If a cell contains a value of 2,400 it will round down to 2, whereas if it contains a value of 2,600 it will round up to 3. Values under 500 will round down to 0.

Posted by Anthony on February 04, 2002 9:53 AM

Almost there.........does it have to round? I mean, is it possible to keep the value of 2,400 even when the custom formatting displays a 2?

Thanks Again


Posted by Scott on February 04, 2002 12:18 PM

The cell will contain the original value (you can see this in the formula bar), and will only display the rounded amount. Any calcs will be off the whole number.

Posted by Anthony on February 05, 2002 7:25 AM