Engineering Notation in Spreadsheets

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

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.

Spreadsheets are for finance guys.

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

Brr. Big complicated spreadsheets are next to impossible to debug.

Cheers

Phil Hobbs

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

RL

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 mean, n,u,m, ,k,M,G... 10^-9 to 10^+9

George H.

My collection of mostly "borrowed" RF related spreadsheets. I find some of them rather handy: Spreadsheets are a great substitutes for scratch pads and back of the envelope calculations.

"How to Format a Cell for Engineering Notation"

"Number Formats in Microsoft Excel" Scroll down to the "Scientific Notation" section.

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.

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

Be wary with the optimiser, it generates incorrect optima with QP problems sometimes.

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 generator".

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

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

Rick

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.

Rick

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!

Rick

StarOffice supports it. Well, 'Scientific' notation, which normalizes the coefficient to -10.0 < a < 10.0. True engineering notation restricts exponents to multiples of three and the coefficient may be in the range -1000.0 < a < 1000.0.

I can live with scientific notation, although I suspect there is a hack to implement true engineering notation as a user defined format.

notation

want

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.

?-)