Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Displaying numbers used in formula | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
How can I stop Excel from displaying rounded numbers? Credit car. | Excel Discussion (Misc queries) |