Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Posts
    92
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Show parts per million in Excel

    I would like to show parts per million in my cells. I don't mind entering it in using exponents, such as 6.02E-6. Easier ways are always nice, but doing it this way is not that much trouble.

    The important thing is that I want to be able to have the units show as ppm, not standard scientific notation. It makes it a LOT easier for people to see that 10 ppm is greater than 9 ppm. It loses impact to compare 1E-5 with 9E-6.

    I discovered in another post to set up the formatting as ######.00E+00 which works fine as long as the PPM value is greater than 1. But if I put in, say, 0.1E-6, I get 100000.00E-12.

    Any suggestions? Just to make things a little more difficult, I must use the actual PPM value in further calculations.
    [I have been here for years; I had to get things restarted]

  2. #2
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Heraklion
    Posts
    541
    Thanks
    2
    Thanked 46 Times in 44 Posts
    Excel has no intrinsic way to accomplish your requirement. However I think you are already on the best track.

    If you use ppb as your "standard" and use the custom format ######### E+00 I believe your users may easily get used to how that looks and what it means - especially if you modify the custom format to ######### E+00 "ppb" which provides a constant visual reminder of what they are looking at.
    Last edited by MartinM; 2011-02-10 at 17:53.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    JohnD1 (2011-02-11)

  4. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts
    I'd do it this way. It's often useful to have a 'hidden' intermediate cell to make entry simpler and output clearer.
    1. In the entry cell (say A1), use a custom format like ##0 "ppm"
    You'd enter 10 ppm as 10 and it would show as 10 ppm
    2. In an intermediate cell (say A2), have an intermediate calculation like =A1/1000000 to get it to its 'real' value
    3. If you don't want to show the intermediate value on the sheet, you could hide the whole row.
    4. In the output cell (say A3), convert the value back to it's 'displayable' form with a formula like =A2*1000000 and a custom format like ##0 "ppm"
    This will turn it back into an output like 10 ppm

    That's how I'd do it.
    It would be nice if Excel had a way to harness what it does with percentages (I think this is really what you're after, but with ppm rather than percentages). If a cell is formatted as a percent format, you type in '10' but it actually stores it as 0.1

    Oh, also to make it easier to show that 4ppm is bigger that 1 ppm, you could use Excel 2010's new sparklines or a formula like =REPT("*", A2) to create a repeat of a star.

    Peter
    Last edited by peterl; 2011-02-10 at 18:42.

  5. The Following User Says Thank You to peterl For This Useful Post:

    JohnD1 (2011-02-11)

  6. #4
    Star Lounger
    Join Date
    Feb 2008
    Posts
    92
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Peter: Your suggestion is my "fall-back" position. However, I was sort of hoping that there was a one-step way of doing it. But thanks to both you and MartinM for the help.
    [I have been here for years; I had to get things restarted]

  7. #5
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Heraklion
    Posts
    541
    Thanks
    2
    Thanked 46 Times in 44 Posts
    I agree that its maybe best to restructure the sheet to have a "display" cell and a "for calculations" cell. Messy but clearer for the users.

    Over the years there have been many regrets that the percentage style of displaying something other than what you type cannot be adapted for other uses, but unfortunately it is in inaccessible code. There are similar issues with the entry of dates, as documented elsewhere in this forum. All of which is just meant to confirm that a workaround is the best you can hope for :-(

    Obviously Microsoft's Excel team has more accountants and mathematicians than chemists !

  8. #6
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    I agree with Martin to have a display section. As a matter of course I desgn spreadsheets with a data section, a calculation section and a display section. each section if offset from the other so if I need to add more columns/rows then the other sections are not affected. HTH
    Paul Coyle
    Approach love and cooking with reckless abandon

  9. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    each section if offset from the other so if I need to add more columns/rows then the other sections are not affected.
    I used to do this in the days of Lotus123 with only 1 sheet / book. With multiple sheets possible I suggest putting each section in a different sheet.

    Steve

Posting Permissions

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