I'm trying to create a workbook capable of doing the maths behind a
biological calculation. The results are very low (x10 E-6) and i would like
to report this with the correct scientic prefix (pico- nano- etc...) is this
possible in excel?

eg result comes as 3.41323E-06

i would really like 3.41 pMol

i can get it down to 2 decimal places but i can't set a rule to 'replace
e-06 with pMol'

On Wed, 12 Oct 2005 09:09:08 -0700, JamesB
wrote:

I'm trying to create a workbook capable of doing the maths behind a
biological calculation. The results are very low (x10 E-6) and i would like
to report this with the correct scientic prefix (pico- nano- etc...) is this
possible in excel?

eg result comes as 3.41323E-06

i would really like 3.41 pMol

i can get it down to 2 decimal places but i can't set a rule to 'replace
e-06 with pMol'

As far as I know, it is not possible to do this with formatting. You would
have to convert the number to a text string, and then substitute the
appropriate prefix.

You could do this with nested SUBSTITUTE statements, TEXT manipulation plus
VLOOKUP to determine the correct prefix, a User Defined Function, etc.

Here is a substitute function for your example above (with your value in A1):

=SUBSTITUTE(TEXT(A1,"0.00E+00"),"E-06","pMol")

All of these methods convert the number to a text string, which will be
difficult to use in subsequent mathematical operations. So the results should
probably be for reporting only.




--ron

Thanks for that Ron - that's working great on the workbook
cheers

"Ron Rosenfeld" wrote:

On Wed, 12 Oct 2005 09:09:08 -0700, JamesB
wrote:

I'm trying to create a workbook capable of doing the maths behind a
biological calculation. The results are very low (x10 E-6) and i would like
to report this with the correct scientic prefix (pico- nano- etc...) is this
possible in excel?

eg result comes as 3.41323E-06

i would really like 3.41 pMol

i can get it down to 2 decimal places but i can't set a rule to 'replace
e-06 with pMol'

As far as I know, it is not possible to do this with formatting. You would
have to convert the number to a text string, and then substitute the
appropriate prefix.

You could do this with nested SUBSTITUTE statements, TEXT manipulation plus
VLOOKUP to determine the correct prefix, a User Defined Function, etc.

Here is a substitute function for your example above (with your value in A1):

=SUBSTITUTE(TEXT(A1,"0.00E+00"),"E-06","pMol")

All of these methods convert the number to a text string, which will be
difficult to use in subsequent mathematical operations. So the results should
probably be for reporting only.




--ron