Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Formatting Numbers so 1,600,000 Displayed as 1.6

    I would like to know if there is a way to format cells, which have numbers exceeding 1 million, so that, for example, 1,600,000 is displayed in a cell as 1.6. The numbers will be used in a graph and I do not want to change their values by dividing by 1 million to get the display effect I am looking for.
    Thanks.
    Jerry

  2. #2
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Hi

    Why not divide by 1,000,000 within your formula with format number and one decimal?
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, Jerry. You can set this up systematically, following Pierre's lead.

    Say your data is in E10:Q15, then leave that data alone, and in E20:Q25, put the formula in E20:

    =E10*Scale

    and copy to the entire range, E20:Q25. Where Scale is a named range (cell) (say in cell B2).

    Then build your graph based on E20:Q25.

    With this set up, you only need change the value in that cell for all formulas to automatically adjust. And for further refinement, set up a lookup table on the Control worksheet, and you can have the Scale cell a formula based on the MAX value of the data in E10:Q15. That way you can have it adjust according to each worksheet that you have to do, and you don't even have to change he value in the Scale, since it is a formula that automatically changes.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Lightbulb

    I thank you both for your responses to the Excel formatting question. I was not aware of this before, but this custom format will accomplish shortening the display of large numbers that I am working with, in the million+ range, without changing the value:
    #,##0.0,,_);[Red](#,##0.0,,)
    Jerry

Posting Permissions

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