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

Kashflow logo
Share this content

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 youve probably got General

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

Put this format into the Type box.

Now, at the far right after the z...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About AccountingWEB

Replies

Please login or register to join the discussion.

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
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
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)
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)

Related content