Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formating in millions (2003)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formating in millions (2003)

    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 <code>0,,.00</code> in the Type box
    - click OK

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

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating in millions (2003)

    Thank you Hans. It worked great.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formating in millions (2003)

    Use this:
    <code>
    0.00,,_);<!t>[Red]<!/t>(0.00,,)
    </code>
    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.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating in millions (2003)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating in millions (2003)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formating in millions (2003)

    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:

    <table border=1><tr><td align=right>

  8. #8
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating in millions (2003)

    Thanks Hans this is what I needed.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating in millions (2003)

    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.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •