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.
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.
Scientific notation is probably the closest you'll get.
On Sat, 22 Jan 2005 12:29:02 -0800, "Joshua Wong" <Joshua
[email protected]> 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.
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" <[email protected]> wrote in message news:[email protected]...
> Scientific notation is probably the closest you'll get.
>
> On Sat, 22 Jan 2005 12:29:02 -0800, "Joshua Wong" <Joshua
> [email protected]> 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.
>
Hi David,
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
webpage? I hope I am not missing something obvious because most of the
software that I use (such as Cadence or ADS) give you an option to format
numbers in engineering notation.
In any event, I am contend to live with the solution that you've provided.
It would be nice if Microsoft could include an option to format numbers in
engineering notation in future releases.
Joshua.
"David McRitchie" wrote:
> 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" <[email protected]> wrote in message news:[email protected]...
> > Scientific notation is probably the closest you'll get.
> >
> > On Sat, 22 Jan 2005 12:29:02 -0800, "Joshua Wong" <Joshua
> > [email protected]> 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.
> >
>
>
>
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" <[email protected]> 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
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" <[email protected]> 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
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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> 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
>
Joshua, I realize it's now 12 years later, and you probably don't need this anymore. But with the new Excel 2016 "SWITCH" function this has become a lot easier to do.
I made an example with 4 significant digits, because that's how I like it.
TLDR: use this formula
Your number in cell A1.Please Login or Register to view this content.
First we need the scientific notation to get the correct exponent (multiples of 3) and amount of digits before and after the decimal:
(cell A2)
=TEXT(A1,"##0.000E+00")
The position of the decimal:
(A4)
=FIND(".",A2)
The position of the exponent:
(A5)
=FIND("E",A2)
The part left of the decimal:
(A7)
=LEFT(A2,A4-1)
The part right of the decimal, shortened with the amount of digits before the decimal:
(A9)
=MID(A2,A4+1,A5-A4-A4+1)
The value of the exponent:
(A11)
=VALUE(RIGHT(A2,3))
The magic, looking at the exponent's value:
(A13)
=SWITCH(A11,-15,"f",-12,"p",-9,"n",-6,"µ",-3,"m",0,",",3,"k",6,"M",9,"G",12,"P",15,"E","???")
The full whammy, combining the results above in what is called Engineering notation:
(A15)
=CONCAT(A7,A13,A9)
And of course you can combine everything into one formula:
Please Login or Register to view this content.
Last edited by Wouter Mense; 09-22-2017 at 08:42 AM. Reason: Long lines of formula's messing with the layout...
14 years later:
I can do it simpler using the ole Log functions, but need 1 helper column:
Base = INT(LOG10(abs(Input))/3)
Output = TEXT(Input/10^(Base*3),"#.000")&MID("fpnµm_kMGPE",Base+6,1) & "V"
You could fold the whole thing into one formula if you wish.
PS I love the new cell naming system. Just type the new unique name in the top left box which usually shows the location.
Ok. Got the Base and Ouput functions to work in the spreadsheet. I am confused on what a Helper Column is however. Rather involved commands unless I can enter them as commands. Is that what you are talking about?
a helper column is just that - a column that is used to do intermediate calcs that you dont (or cant) put in the main formula...it "helps" the main formula.
Having said that though, Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks