Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Joshua Wong
 
Posts: n/a
Default displaying numbers in engineering notation

Hi,

I am wondering if there is a way to display numbers in engineering notation?
ie. 3M if the number is 3e+6
or 5u if the number is 5e-6

Best regards,
Joshua.
  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

Scientific notation is probably the closest you'll get.

On Sat, 22 Jan 2005 12:29:02 -0800, "Joshua Wong" <Joshua
wrote:

Hi,

I am wondering if there is a way to display numbers in engineering

notation?
ie. 3M if the number is 3e+6
or 5u if the number is 5e-6

Best regards,
Joshua.


  #3   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Joshua,
Exactly what you asked for:
Use ##0.0E+0

More on cell formatting see first large table in
http://www.mvps.org/dmcritchie/excel...htm#cellformat
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Myrna Larson" wrote in message ...
Scientific notation is probably the closest you'll get.

On Sat, 22 Jan 2005 12:29:02 -0800, "Joshua Wong" <Joshua
wrote:

Hi,

I am wondering if there is a way to display numbers in engineering

notation?
ie. 3M if the number is 3e+6
or 5u if the number is 5e-6

Best regards,
Joshua.




  #4   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Joshua,
This will get you the characters you ask for but if involves a helper column,
and the result is text, which maybe is what you need for your other program.

=SUBSTITUTE(TEXT(A16,"##0.0E+0"),"E","p")

or if you want E- but not E+ use
=SUBSTITUTE(TEXT(A16,"##0.0E-0"),"E","p")

Why the other letters?
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joshua Wong" wrote
Thank you for the pointer, it was very helpful. The format string
displays numbers in engineering notation in that the exponent part is always
in the power of 3. But I was hoping that there is a way to replace the
exponent part with suffixes such as p, n, u, m, k, M, G, T ...etc

So is it trivial to do this without writing VB code as shown on your




  #5   Report Post  
Joshua Wong
 
Posts: n/a
Default

Hi David,

Sorry, I wasn't being specific. When I was referring to the exponent, I
meant
the letter 'E' and the number. For example: E+9 or E-6

In engineering notation, the exponent is denoted by a letter and has the
following mapping:

123E-15 - 123 f
123E-12 - 123 p
123E-9 - 123 n
123E-6 - 123 u
123E-3 - 123 m
123E0 - 123
123E3 - 123 k
123E6 - 123 M
123E9 - 123 G
123E12 - 123 T
and so on...

I need to analyse data that spans across large orders of magnitude, as a
result it is much more intuitive for me to read numbers in engineering
notation.

Regards,
Joshua.

"David McRitchie" wrote:

Hi Joshua,
This will get you the characters you ask for but if involves a helper column,
and the result is text, which maybe is what you need for your other program.

=SUBSTITUTE(TEXT(A16,"##0.0E+0"),"E","p")

or if you want E- but not E+ use
=SUBSTITUTE(TEXT(A16,"##0.0E-0"),"E","p")

Why the other letters?
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joshua Wong" wrote
Thank you for the pointer, it was very helpful. The format string
displays numbers in engineering notation in that the exponent part is always
in the power of 3. But I was hoping that there is a way to replace the
exponent part with suffixes such as p, n, u, m, k, M, G, T ...etc

So is it trivial to do this without writing VB code as shown on your




  #6   Report Post  
David McRitchie
 
Posts: n/a
Default

Hi Joshua,
Thanks for the explanation.

Looks like something possibly for electrical engineering, but is it really
called engineering notation by anyone.

You can't do that by formatting, you would have to use programming,
and assuming that the values would change that would have to be
a User Defined Function (UDF). If I were writing it, I would have
the UDF work from the text value returned from the VBA format
statement (the equivalent of the TEXT Worksheet Function).

Fortunately, I think, you already indicated that what was supplied
would be preferable to programming.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joshua Wong" wrote in message ...
Hi David,

Sorry, I wasn't being specific. When I was referring to the exponent, I
meant
the letter 'E' and the number. For example: E+9 or E-6

In engineering notation, the exponent is denoted by a letter and has the
following mapping:

123E-15 - 123 f
123E-12 - 123 p
123E-9 - 123 n
123E-6 - 123 u
123E-3 - 123 m
123E0 - 123
123E3 - 123 k
123E6 - 123 M
123E9 - 123 G
123E12 - 123 T
and so on...

I need to analyse data that spans across large orders of magnitude, as a
result it is much more intuitive for me to read numbers in engineering
notation.

Regards,
Joshua.

"David McRitchie" wrote:

Hi Joshua,
This will get you the characters you ask for but if involves a helper column,
and the result is text, which maybe is what you need for your other program.

=SUBSTITUTE(TEXT(A16,"##0.0E+0"),"E","p")

or if you want E- but not E+ use
=SUBSTITUTE(TEXT(A16,"##0.0E-0"),"E","p")

Why the other letters?
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Joshua Wong" wrote
Thank you for the pointer, it was very helpful. The format string
displays numbers in engineering notation in that the exponent part is always
in the power of 3. But I was hoping that there is a way to replace the
exponent part with suffixes such as p, n, u, m, k, M, G, T ...etc

So is it trivial to do this without writing VB code as shown on your





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
Displaying numbers used in formula SeeFar Excel Discussion (Misc queries) 2 December 28th 04 08:05 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
Sorting imported "numbers" Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 08:33 PM
How can I stop Excel from displaying rounded numbers? Credit car. OldManP Excel Discussion (Misc queries) 3 December 8th 04 08:52 PM


All times are GMT +1. The time now is 11:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"