» Rounding Numbers to Thousands
CATEGORY - Excel Format
VERSION - All Microsoft Excel Versions
1. Press Ctrl+1 to open the Format Cells dialog box.
2. Select the Number tab, and from Category, select Custom.
3. In the Type box, enter the following Custom Formatting syntax:
#,##0, ;[Red](#,##0,);- ;
Result:
• Original number: 5,645,625
• The displaying formatted number: 5,646
Note:
The number formatting syntax is:
Positive; Negative; 0; Text
Book Store:
Recommended Books:
- The Ernst & Young Business Plan Guide
- The Financial Numbers Game: Detecting Creative Accounting Practices
- Financial Statement Analysis with S&P insert card
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
- Special Edition Using Microsoft Outlook 2002
- Essentials of Accounting and Post Test Booklet 8, Eighth Edition
Thousands style
Ken Wolfe Posted on: 31-12-1969
I followed the instructions as they're described here into the "type" box, but it doesn't appear down with the other formats and I'm not able to select it. And I missing something?Thousands style
Connie Posted on: 31-12-1969
After the mask is typed in and I click on OK, an entry in the sell of 11,321 produces "0 (11)" sans the quotes, with the entry in red. A check of the mask finds it in the following format: "[Red]##,#0_;(#,##0,);- ;." Me thinks more information is needed.Thousands style
Ken Martin Posted on: 31-12-1969
I have been using the following formula: #,###,;[Red](#,###,);- ; This seems to work. (PS leaving out the - ; at the end will return a blank cell when a zero is entered.)Display only, not rounding
Mark Posted on: 31-12-1969
I believe this format only changes the way the data is displayed. True rounding changes the value stored in the cell.Excel Defaults (modified)
Rakesh Latchana Posted on: 31-12-1969
I use the "comma" style which comes with excel by default. It reads _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_) and if you replace the two .00 with a comma , it would provide you with numbers rounded to the nearest thousand.Rounding Formulas
Simon Posted on: 31-12-1969
=INT(ROUND(A1*1000,0))/1000
e.g 1000.987654 is changed to 1000.988
=INT(ROUND(A1,0))
e.g 1000.987654 is changed to 1000.1
Rounding Formulas
Simon Posted on: 31-12-1969
=INT(ROUND(A1,0))
e.g 1000.987654 is changed to 1001
(Whoops!)
Mr.
Rodrigo Moran Posted on: 31-12-1969
My version of this tip is:
A1=13546,67
round(A1/1000;0)*1000 will yield 13000 as a result.
Excel's own solution
Geoff Posted on: 31-12-1969
I thought
=ROUND(A1,-3)
would do the trick
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
