Share this content
Tags:

Excel 2min Tip #8. Round your budgets to the nearest thousand . By David Carter

7th Sep 2007
Share this content
Kashflow logo

If you have a spreadsheet containing a lot of numbers, it will be easier for managers to read if you round them to the nearest thousand.

To display figures to the nearest thousand, go into Format – Custom, and add three things – a dot, a zero and a comma

Example:
To display these numbers:

1021.76...........1.1
560...................0.6
540...................0.5
21.....................0.0
1345.26...........1.3
-520.78...........-0.5

Type the first column of numbers into Excel, column A. Then copy them into column B.

Highlight column B.

From main menu: Format – Cells – Custom (at the bottom)

In the Type box you’ve probably got General

Find the format #,###0;[Red]-#,###0 (in Excel 2003, it’s at the bottom of the screen)

Put this format into the Type box.

Now, at the far right after the zero, insert a dot, then a zero, then a comma

It should now look like this: #,###0;[Red]-#,###0.0,

Then do the same thing after the zero just before the semi-colon, so that it now looks like:

#,###0.0,;[Red]-#,###0.0,

Make sure you get the sequence right: dot – zero – comma

Press OK. The numbers are now nicely rounded to the nearest thousand.

END

Tags:

Replies (4)

Please login or register to join the discussion.

Simon Hurst
By Simon Hurst
08th Sep 2007 09:55

Number formats
John - you might find the formatting section towards the bottom of our Excel Compendium dates & numbers article useful.

Simon Hurst

Thanks (0)
avatar
By David Carter
09th Sep 2007 00:11

dot - zero - comma
Well, I use the "dot-zero-comma" slogan because I just worked it years ago out via suck it and see. I think the meaning is:

dot = add decimal places

0 = add a single decimal place, and if it's 0 display 0
( the difference between # and 0 is that # means if it's zero, don't print anything)

comma = round to the nearest thousand. I think two commas = to the nearest million

We need Richard Scoullar. He'll know.
.

Thanks (0)
avatar
By David160
11th Sep 2007 17:21

Adding up rounded figures
If you then add up those rounded figures you do not neccessarily get the correct figure, even if you apply the rounding format as described to the total. To do that you need to use one of the rounding formulae such as Round(number,places to round to) on each number to get the correct total. Play around with Round formulae and format in Excel to see what I mean.

Thanks (0)
Simon Hurst
By Simon Hurst
11th Sep 2007 18:33

Rounding including thousands
I included some information on rounding, including working with thousands in one of my blog entries which might help

http://kitss.wordpress.com/2007/03/27/its-your-round/

Simon Hurst

Thanks (0)