New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Format thousands

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Re: Format thousands

Postby johnny009 » Tue Apr 19, 2016 1:46 am

Solved: #.##0.
  • 0

johnny009
Rookie
 
Posts: 6
Joined: Apr 18, 2016
Reputation: 0
Excel Version: 2007

Re: Format thousands

Postby Sisyphus » Sat Apr 23, 2016 6:56 am

The workbook you posted was created on a German speaking PC, where period and comma are used in reversed purpose than in English. The numbers appear different on my PC which speaks English. For example, I see 570,947,422.10 in your worksheet's cell A1. You can change the setting on your PC from the Control Panel -> Change Date, Time and Number Formats. Excel will pick the format from whatever you set there.

At that, the task at hand is to divide the number you have by 1000 and to round it.
570,947,422.10 / 1000 = 570,947.42210
and if you round this number 4/5, the decimals 0.42210 will be dropped. You haven't stated your rounding preference. By default, Excel will round 4/5 - meaning 4 down and 5 up. I suggest that you use a formula, like =A1/1000, to execute the division. Mind that A1/1000 produces a result of 570947.4221 and =ROUND(A1/1000,0) produces 570947. Internally, Excel recognises only the decimal point which will be displayed as a comma on German speaking machines.

Depending upon how you executed the division, Excel "sees" this number as 570947 or 570947.4221 internally. The question now is how you want to see it on your screen. This is done giving the cell which displays the number a format (Format -> Cells, which is available from the ribbon or from the right-click menu). Give the output cell a Custom format. If you want to see the number on a German speaking machine you can format the cell with "#.##0" (omit the quotation marks). If your PC is English speaking, use the format "###\.##0" (also without quotation marks. On a German speaking PC the result will be exactly the same.

But if you move your workbook from a German machine to an English one, the number will be displayed as 570,947 if you gave it the local, German format. Excel automatically replaces the German period with an English comma for the English speaking user. On the other hand, if you used the format ###\.##0 the number will be displayed as 570.947 on any machine, and this will be misunderstood by English speaking people. If you use an English speaking machine and want to produce numbers for a German reading public, you can format as #,##0 (using a standard, built-in format, not even a Custom one) and Excel will display the number as 570.947 (with the period) on a German speaking PC although you see 570,497 (with a comma) on yours.

Whether or not you rounded the number, dropping the decimals .42210 makes no difference in the cell where the number is displayed, but if you continue to use that number in calculations, such as additions or multiplications, the decimals will continue to be used, even though they aren't visible.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Previous

Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 76 guests

cron