formating in millions (2003)

Topic

New Reply

Hello,

I have a spreadsheet that has a cell with the number $209,864,250. I need to link this cell to another worksheet which I am able to do. The problem is that on the other spreadsheet I would like the cell be formatted to show $209.86 which is the amount in millions. Any help would be great.

Reply | Quote

Replies

Welcome to Woody’s Lounge!

You can use a custom number format:

– select the cell
– select Format | Cells…
– select the Custom category in the Number tab
– enter 0,,.00 in the Type box
– click OK

The commas ,, tell Excel to display millions (one comma would display thousands), and the .00 specifies that you want to display 2 digits after that.

Reply | Quote

Thank you Hans. It worked great.

Reply | Quote

Hans,

One more question. How do I have it so it formats with the same as above, but in case the number is negative with the parenthesis and in red? I also have a sum that does not add the totals right. It seems that it is of by 1. How can I fix this? Thanks.

Reply | Quote

Use this:

0.00,,_);[Red](0.00,,)

The first section is for positive numbers (and zero), the second section after the semi-colon for negative numbers. The _) in the first section adds a space after the number with the same width as a closing paretnhesis ), so that positive and negative numbers are aligned correctly.

Reply | Quote

Thanks Hans for the information. How about when I have a sum, it seems that the addition is short by 1 everytime? Any ideas on how to fix this. By the way thanks for all the help and I hope I am not asking too much.

Reply | Quote

When you format numbers as in this thread, you are rounding the displayed value. The actual number stored by Excel has more digits than are shown on screen. Excel performs all calculations by default using the stored values, not the displayed values. This can lead to apparent discrepancies in calculations. Here is an example:

Reply | Quote

Thanks Hans this is what I needed.

Reply | Quote

In addition to what Hans said, using the Precision as displayed option will affect all values in the workbook, and this can cause other unexpected results. If you don’t want to use that option, then there is another solution. If the original value (the cell where the $209,864,250 is) is in cell A1, then in the cell where you want the value in millions you can use the formula =ROUND(A1/100000,2). Then format that cell normally, not with the special format Hans gave you. You should now be able to add up the values and get the expected answer, and you haven’t affected the precision of any other values in the workbook.

Reply | Quote