Connect with us

Engineering Notation in Spreadsheets

Discussion in 'Electronic Design' started by rickman, Nov 27, 2012.

Scroll to continue with content
  1. rickman

    rickman Guest

    So what is up with the seeming lack of support for engineering notation
    in spread sheets? I can do all sorts of manipulation of the display
    format, I can even do conditional formatting to change the color, but I
    don't see any direct support for engineering notation as opposed to
    scientific notation.

    Do they think engineers don't use spread sheets or do they think we want
    to write our own modules for this?

  2. Jeroen

    Jeroen Guest

    I've been complaining about that for years! And that goes for numerical
    IO routines, math programs, graphing tools, CAE tools, etc, etc,
    as well.

    What's so hard about using SI prefixes? It makes very small and
    very large numbers *so* much easier to read. As far as I know,
    only Gnuplot and Spice make an attempt, and while the former is
    merely clumsy, the latter gets it wrong! Shame!

    As for some well-known spreadsheets, eye candy comes first, is all.

    Jeroen Belleman
  3. Rich Webb

    Rich Webb Guest

    Try a custom format: ##0.000E+00

    That seems to work in Excel 2010 and PlanMaker 2012 to yield a
    power-of-three exponent and rounding to three decimal places. Didn't
    work in LibreOffice 3.5, though; just got a bunch of hash marks.
  4. Joerg

    Joerg Guest

    I have a client where they successfully calculate and simulate large
    sections of engines and stuff in Excel.
  5. legg

    legg Guest

    Hell, you can't even get recognition of signifigant figures in
    non-scientific notation, unless perhaps you turn it into dollars and

  6. I've got an old copy of origin that I use for graphing and spread-
    sheet type stuff.
    It does all sorts of notation, including 'engineering' if by that you
    n,u,m, ,k,M,G... 10^-9 to 10^+9

    George H.
  7. miso

    miso Guest

    But most engineering spreadsheets are not that complicated. I think you
    are doing yourself a disservice if you ignore spreadsheets over say
    custom programs.

    Excel has an excellent optimizer in it. Well at least in Office 2000. It
    is not installed by default, so you need to dig up the installation
    disk. I had to design an arithmetically (as opposed to geometrically)
    symmetric bandpass filter. I used the optimizer to do this. The error
    function (what you try to drive to zero) can be changed dynamically,
    which is something most optimizers can't do.

    If you are curious, this type of bandpass needs transmissions zeroes.
  8. Sure, but I want to be able to tell it to display 47e-9 as
    '47n' and 4.7E12 as '4.7T' or perhaps '4T7', etc. There is
    no way to do that, as far as I can tell.

    Jeroen Belleman
  9. Joerg

    Joerg Guest

    Scope around for VBA routines. Maybe someone has written one for this
    case and posted it on the web. But those will only run on native Excel,
    not on OpenOffice and such.

    I am not a programmer but have used VBA. It's amazing, you can tell your
    spreadsheet "I want the result in D83 to be piped to the function
  10. OK, it's certainly possible to whip up something using the CHOOSE
    and LOG10 functions to print numbers with SI prefixes, but it's
    tedious. I haven't really thought about input yet.
    But it should be a standard thing; Not something that I have to
    write myself and carry over from sheet to sheet.

    Jeroen Belleman
  11. OK, it's certainly possible to whip up something using the CHOOSE
    and LOG10 functions to print numbers with SI prefixes, but it's
    tedious. I haven't really thought about input yet.
    But it should be a standard thing; Not something that I have to
    write myself and carry over from sheet to sheet.

    Jeroen Belleman
  12. Guest

    It can be done by brute force (if-then-multiply-concatenate).
  13. rickman

    rickman Guest

    I tried this in open office and it forces the number to three digits to
    the left of the decimal point.

  14. rickman

    rickman Guest

    I tried ##0.0E+0 in excel and it seems to work as well as your format.
    The magic seems to be in the ##0. portion. In excel it says use up to
    three digits which seems to be a trigger for engineering notation. In
    Open Office it seems to say use exactly three digits which is not
    engineering notation.

    +1 for excel... damn!

  15. rickman

    rickman Guest

    Looks like my frustration was unfounded. It seems there is already
    support for Engineering notation in excel even if it isn't documented as
    well as a method of generating Engineering notation in Open Office.

    Excel supports it by using three place holders in front of the decimal
    point in a scientific notation format, e.g. ##0.0E0

    To see how to do it in Open Office, check out this issue page and search
    for the file name EngineeringV2.ods

    A couple of posts down is a macro for a hot key to automatically apply
    the conditional formatting rather than copying it around.

    I added the basic format to my spread sheet using the info provided and
    it seems to work correctly when applied as a style. If they supported
    it natively it might be a little more flexible by allowing the displayed
    number of digits to be adjusted using the toolbar, etc. But this is
    good enough for now.

    Maybe someone will work on the SI prefixes next!

  16. josephkk

    josephkk Guest

    Once upon a time (about 30 years ago) i wrote controlled precision (to
    match the accuracy of the measurement) scientific notation output with
    controlled column placement. It was a bit of a pain testing it, but i did
    get it working right.

  17. Fred Abse

    Fred Abse Guest

    I've been trying to do it for years in StarOffice. No luck. If you find a
    way, please, pretty please, share it ;-(
  18. rickman

    rickman Guest

    Isn't StarOffice the same as OpenOffice? A couple of days ago I posted
    info on a solution I found. It isn't overly simple, but it works and
    there is a like to a macro that will at least let you assign it to a
    button if you want.

  19. Rich Webb

    Rich Webb Guest

    I'd forgotten that it could do this but there's an app for Windows and
    OS X that handles engineering suffixes and significant figures properly.
    That is, enter "1/pi^2" and get "101.3m" as the result, or try "1/2n"
    and get "500.0M". Also hex/binary, unit conversions, and user-defined
    functions. Quite the handy little tool.
  20. rickman

    rickman Guest

    Is that Windows and OS X or Excel under those two OS?

Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Electronics Point Logo
Continue to site
Quote of the day