Format a number as ppm

[hmm]

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?

 

[Mike]

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.

 

[Bernard Liengme]

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

 

[hmm]

Thanks Mike & Bernard. I knew it wasn't straighforward; it was worthwhile
asking.

 

[Chip Pearson]

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)

 

[hmm]

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".

 

[Sandy Mann]

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

 

[hmm]

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.