rounding to thousands in a spreadsheet
Posted by Gary Neubauer on June 29, 2001 8:48 AM
I have a spreadsheet with dollars in whole numbers, for example, 65231. I'd like to round all values in the spreadsheet into rounded thousands, eg. to 65. However, I'd like to have the total columns represent the rounded total of the numbers. For example, 1300, 1300, and 1400 would be displayed as 1, 1, and 1, but the total would be 4. Is there a utility I can add to have the entire spreadsheet reflect these new values? Or, a formula for the entire spreadsheet? Also, have the option to round the 65231 to 65.2
Thanks!
Posted by IML on June 29, 2001 9:00 AM
Partial answer
For the first part, you can accomplish this by choosing your cells and using the following custom format
#,
I'm not sure how to show it to the hundreds place with formatting, though. Hopefully this will get you started though.
Posted by to hundreds on June 29, 2001 9:15 AM
Give this format a whirl for the hundreds place,
0.0,
Posted by Joe Was on June 29, 2001 10:17 AM
I use a formula to overcome this problem.
In cell B1: =IF(A1>0,ROUND(A1,-3),0)
this gives the answer in thousands.
You can also use the INT formula to round up.
The ROUND syntax is ROUND(Formula or Address,code)
code: 0 = nearest one place.
1 = nearest 1/10th
2 = nearest 1/100th
3 = nearest 1/1,000th
-1 = nearest ten's
-2 = nearest hundred
-3 = nearest thousand
etc.
You can nest the ROUND function as much as you want, to condition elements of your formula.
If the rounded values are in their own cells and you sum the rounded values the sum will be the way yoy want. If you want the rounding to be in the data cells then you need to use the format options. With the format options only the display is rounded, your answer in a sum of these cells well be the sum of the original values rounded. JSW
