Maker Pro
Maker Pro

Engineering Notation in Spreadsheets

R

rickman

Jan 1, 1970
0
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?

Rick
 
J

Jeroen

Jan 1, 1970
0
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?

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
 
R

Rich Webb

Jan 1, 1970
0
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?

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.
 
J

Joerg

Jan 1, 1970
0
Tim said:
Spreadsheets are for finance guys.

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

legg

Jan 1, 1970
0
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?

Rick

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

RL
 
G

George Herold

Jan 1, 1970
0
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?

Rick

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.
 
M

miso

Jan 1, 1970
0
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.
 
J

Joerg

Jan 1, 1970
0
Jeroen said:
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.

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

Jeroen Belleman

Jan 1, 1970
0
In the custom programs my oldest son writes for me, they come out that
way ;-)

I subscribe to this list...

http://archive.aweber.com/ctxtips01

for Excel tips.

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
 
J

Jeroen Belleman

Jan 1, 1970
0
In the custom programs my oldest son writes for me, they come out that
way ;-)

I subscribe to this list...

http://archive.aweber.com/ctxtips01

for Excel tips.

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

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

rickman

Jan 1, 1970
0
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.

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

Rick
 
R

rickman

Jan 1, 1970
0
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.

+1 for excel... damn!

Rick
 
R

rickman

Jan 1, 1970
0
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?

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

http://forum.openoffice.org/en/forum/viewtopic.php?t=16858&p=172214

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
 
J

josephkk

Jan 1, 1970
0
I tried this in open office and it forces the number to three digits to
the left of the decimal point.

Rick

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.

?-)
 
F

Fred Abse

Jan 1, 1970
0
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.

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

rickman

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

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.

Rick
 
R

Rich Webb

Jan 1, 1970
0
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?

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. http://www.zoesoft.com/
 
R

rickman

Jan 1, 1970
0
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. http://www.zoesoft.com/

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

Rick
 
Top