Format a number as ppm

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have numbers I would like expressed as parts per million (ppm). Just as %
format displays the number multiplied by 100, ppm format should display the
number multiplied by 1 million (and append the unit "ppm"). Is there a way I
can enter this as a custom format, including in charts?
 
The PPM bit can be appended by using

0" PPM"

as a custom format for the cell. However multiplying by a million is a
different matter it can be done but would be very confusing. For example if
you set up automatic multiplication by 1,000,000 then entering 3 would give a
result of 3m parts per million which I don't think is what you are aiming
for. Please post again if this isn't what you want.
 
Excel is not very good with small values. You can format 5,500,000 to show
as 5.5; you can scale chart axes by thousands and millions but factors below
1 are not available. All I can suggest is =TEXT(A1*1000000,"#.00")&" ppm"
but this will be text so no calculations can be performed on it.
best wishes
 
Try the custom number format:

0,," ppm";-0,," ppm";@


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
Thanks Chip.

It got the "ppm" part right, but it divides the entered number by a million
instead of multiplying. Also, the number represented by a figure expressed
in ppm is very low; for example, 100 ppm represents 0.0001. When I entered
such values in the cell, it read "0 ppm".
 
Perhaps Chip will come up with something clever but when I was using ppm for
the calculations for the Hydrotherapy pool, I formateed the cell as: General
" ppm"
I then entered - say - 2.5 for 2.5 ppm and divided the cell value by
1,000,000 every time that I use it in a calculation.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Here is the ideal, intended as a suggestion for Microsoft or anyone else who
wishes to tackle it:

1. If the user enters " ppm" after a number, Excel will understand it to be
a number whose value is 1 million times less than the part before " ppm";
this is similar to a cell entry consisting of a number followed by "%".

2. Another option: a format that if the value is less than 1 ppm (i.e.
1e-6), Excel appends " ppb" (parts per billion), preceded by one billion
times the value. (Behaving similarly to item 1 above if the user enters a
number followed by " ppb".) If less than 1 ppb (1e-9), " ppt", parts per
trillion, with features similar to above.
 
Back
Top