Excel problem with commas?

Question

I've got a little problem with Excel.
I am trying to use the SUM function to find the sum of a group of cells but the decimals and commas are screwing me.
The units I am dealing with are millions of dollars (I am not a Wall Street accountant but a university student) so something I entered in the table as 23,500 should be equal to 23.5 billion (as in 23 thousand five hundred million dollars), not 23 and a half million dollars. This I have got sorted out but when I enter something without decimal places like 969, Excel reads it as 969,000 or 969 billion. How do I stop this from happening?

Answer 1

It sounds like you have got a setting mixed up. Comma is used in some countries to mean the decimal marker. (Other countries use the full-stop or period).



So in Germany 23,500 means 23 and a half (and a question why you put 2 extra 0).

IN England & US, 23,500 means 23 thousand 50 hundred.



In Excel, if you want to show the commas (meaning separation of the 1000 units), go to Format/Cells/Number and tick "use 1000 separator"

Then enter "23500" and it will be shown as 23,500 (or 23.500 if you are using comma as the decimal marker)



You might also have the cell setting showing extra decimal places, so the 969,000 might be using the comma as decimal marker, and 3 decimal places. Again, go to Format/Cells/Number and select the number of dec. places you want. Try "2" and if this changes your 969,000 to 969,00 then you're definitely using comma for the decimal place.



Edit: just seen your question clarification

In Excel 2007, go to Excel Options (the big round button, then the bottom of the menu) / Advanced section, Editing Options and find the setting "use system separators". Clear the Use system separators check box. Type new separators in the Decimal separator and Thousands separator boxes.





In Excel 2003, Tools / Options/International tab.

Under Number Handling, clear the Use system separators check box. Type new separators in the Decimal separator and Thousands separator boxes.

Answer 2

Hi,

You should use actual figures-> Like 23.5 OR first calculate and then divide the cell by 1000 to get the figues in million.

In fact 23,500 is format for the cell you want to look. For calculation, you have to divide. Similarly, number of decimal places is also a format for a cell or group of cells.

By the there is 'help' available, you get help 'F1' or Formula help.



You are mixing cell format with formula. Please be clear and dont mix those. 999,000 is a format. If needed then format like 999.000.

And divide the value by 1000 to get correct result.



Please NOTE that ',' belong to cell format and has relation to calculations. For calculations, you have add, divide, multiply and subtract. These are used in formulas.

Best of luck.









With regards.

Answer 3

i ought to locate purely one thank you to try this. choose all the cells with the commas which you rather desire to alter to dots and alter the formatting to text fabric. Then, click on the homestead tab, locate and decide, click on replace, and enter a comma interior the locate and a era interior the replace fields. After this has been accomplished, exchange the formatting of those cells to style and the numbers must be in simple terms right. All tries to apply the guy made and LEFT, MID combos did no longer artwork continuously.