Engineering Notation in Spreadsheets

rickman

unread,

Nov 27, 2012, 9:28:02 AM11/27/12

to

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

Jeroen

unread,

Nov 27, 2012, 3:59:46 PM11/27/12

to

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

Rich Webb

unread,

Nov 27, 2012, 4:09:11 PM11/27/12

to

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.

--
Rich Webb Norfolk, VA

Tim Wescott

unread,

Nov 27, 2012, 4:25:25 PM11/27/12

to

Spreadsheets are for finance guys.

--
My liberal friends think I'm a conservative kook.
My conservative friends think I'm a liberal kook.
Why am I not happy that they have found common ground?

Tim Wescott, Communications, Control, Circuits & Software
http://www.wescottdesign.com

Joerg

unread,

Nov 27, 2012, 4:29:30 PM11/27/12

to

Tim Wescott wrote:
> On Tue, 27 Nov 2012 09:28:02 -0500, rickman wrote:
>
>> 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
>
> Spreadsheets are for finance guys.
>

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

--
Regards, Joerg

http://www.analogconsultants.com/

Phil Hobbs

unread,

Nov 27, 2012, 4:33:48 PM11/27/12

to

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

Cheers

Phil Hobbs

--
Dr Philip C D Hobbs
Principal Consultant
ElectroOptical Innovations LLC
Optics, Electro-optics, Photonics, Analog Electronics

160 North State Road #203
Briarcliff Manor NY 10510

hobbs at electrooptical dot net
http://electrooptical.net

legg

unread,

Nov 27, 2012, 5:06:39 PM11/27/12

to

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

RL

Jim Thompson

unread,

Nov 27, 2012, 7:46:42 PM11/27/12

to

On Tue, 27 Nov 2012 21:59:46 +0100, Jeroen <jer...@nospam.please>
wrote:

George Herold

unread,

Nov 27, 2012, 8:47:15 PM11/27/12

to

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.

Jeff Liebermann

unread,

Nov 27, 2012, 11:55:47 PM11/27/12

to

On Tue, 27 Nov 2012 15:25:25 -0600, Tim Wescott <t...@seemywebsite.com>
wrote:

>Spreadsheets are for finance guys.

My collection of mostly "borrowed" RF related spreadsheets. I find
some of them rather handy:
<http://802.11junk.com/jeffl/rf-calc/>
Spreadsheets are a great substitutes for scratch pads and back of the
envelope calculations.

"How to Format a Cell for Engineering Notation"
<http://www.tek-tips.com/faqs.cfm?fid=7052>

"Number Formats in Microsoft Excel"
<http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=414>
Scroll down to the "Scientific Notation" section.

--
Jeff Liebermann je...@cruzio.com
150 Felker St #D http://www.LearnByDestroying.com
Santa Cruz CA 95060 http://802.11junk.com
Skype: JeffLiebermann AE6KS 831-336-2558

miso

unread,

Nov 28, 2012, 12:30:50 AM11/28/12

to

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.

Jeroen Belleman

unread,

Nov 28, 2012, 2:32:15 AM11/28/12

to

On 2012-11-28 01:46, Jim Thompson wrote:
> On Tue, 27 Nov 2012 21:59:46 +0100, Jeroen<jer...@nospam.please>
> wrote:
>
>> On 2012-11-27 15:28, rickman wrote:
>>> 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
>
> http://tinyurl.com/c2owzpc
>
> ...Jim Thompson

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

Bruce Varley

unread,

Nov 28, 2012, 9:25:50 AM11/28/12

to

"miso" <mi...@sushi.com> wrote in message
news:k947id$vm1$1...@speranza.aioe.org...

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

Jim Thompson

unread,

Nov 28, 2012, 10:32:07 AM11/28/12

to

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.

Joerg

unread,

Nov 28, 2012, 11:22:18 AM11/28/12

to

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

Jeroen Belleman

unread,

Nov 28, 2012, 11:40:16 AM11/28/12

to

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

Jeroen Belleman

unread,

Nov 28, 2012, 11:44:15 AM11/28/12

to

On 2012-11-28 16:32, Jim Thompson wrote:

k...@att.bizzz

unread,

Nov 28, 2012, 12:14:38 PM11/28/12

to

On Wed, 28 Nov 2012 08:32:15 +0100, Jeroen Belleman
<jer...@nospam.please> wrote:

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

rickman

unread,

Nov 29, 2012, 11:10:19 AM11/29/12

to

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

Rick

rickman

unread,

Nov 29, 2012, 11:26:17 AM11/29/12

to

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

rickman

unread,

Nov 29, 2012, 12:38:49 PM11/29/12

to

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

Paul Hovnanian P.E.

unread,

Nov 30, 2012, 7:05:33 PM11/30/12

to

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.

--
Paul Hovnanian mailto:Pa...@Hovnanian.com
------------------------------------------------------------------
Speed is n0 subsittute fo accurancy.

josephkk

unread,

Dec 1, 2012, 12:53:11 AM12/1/12

to

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.

?-)

Message has been deleted

Rich Webb

unread,

Dec 3, 2012, 2:05:44 PM12/3/12

to

On Tue, 27 Nov 2012 09:28:02 -0500, rickman <gnu...@gmail.com> wrote:

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/

rickman

unread,

Dec 2, 2012, 10:46:28 PM12/2/12

to

On 12/1/2012 4:11 PM, Fred Abse wrote:

> On Fri, 30 Nov 2012 16:05:33 -0800, Paul Hovnanian P.E. wrote:
>
>> 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 ;-(
>

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

rickman

unread,

Dec 3, 2012, 5:01:08 PM12/3/12

to

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

Rick

Rich Webb

unread,

Dec 3, 2012, 5:10:19 PM12/3/12

to

Windows and Mac OS X. It's a stand-alone "console calculator" for
quickies like
> 1/(2*pi*234.5k*67.8p)
ans = 10.01k
Not an Excel add-on (and not Linux, other than under Wine).

josephkk

unread,

Dec 3, 2012, 10:49:20 PM12/3/12

to

On Sun, 02 Dec 2012 22:46:28 -0500, rickman <gnu...@gmail.com> wrote:

>On 12/1/2012 4:11 PM, Fred Abse wrote:
>> On Fri, 30 Nov 2012 16:05:33 -0800, Paul Hovnanian P.E. wrote:
>>
>>> 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 ;-(
>>
>
>Isn't StarOffice the same as OpenOffice?

Not in the least, it is the remnants of WordPerfect, a spreadsheet (maybe
old Lotus 123), some Corel image applications and something else. Never
was open source. I used to have a copy.

rickman

unread,

Dec 4, 2012, 2:02:30 PM12/4/12

to

On 12/3/2012 10:49 PM, josephkk wrote:
> On Sun, 02 Dec 2012 22:46:28 -0500, rickman<gnu...@gmail.com> wrote:
>
>> On 12/1/2012 4:11 PM, Fred Abse wrote:
>>> On Fri, 30 Nov 2012 16:05:33 -0800, Paul Hovnanian P.E. wrote:
>>>
>>>> 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 ;-(
>>>
>>
>> Isn't StarOffice the same as OpenOffice?
>
> Not in the least, it is the remnants of WordPerfect, a spreadsheet (maybe
> old Lotus 123), some Corel image applications and something else. Never
> was open source. I used to have a copy.

I guess I got them confused a long time ago. StarOffice is free, as in
"free beer" though, right? Or maybe I've just plain got it mixed up
entirely. I seem to recall having a free copy of StarOffice a long time
ago.

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

BTW, that should be "and there is a *link* to a macro" in the paragraph
above.

Rick

Phil Hobbs

unread,

Dec 4, 2012, 2:10:35 PM12/4/12

to

StarOffice was the original Sun product, which stank. They open-sourced
it, and a fork became OpenOffice. Later StarOffice versions were based
on OpenOffice, and according to Wiki, you can still buy an OpenOffice
version from Oracle if you really want to.

WordPerfect got bought by Corel, and is still available AFAIK. Not the
same product at all.

Cheers

Phil "DOS WP 5.1+ forever" Hobbs

Rich Webb

unread,

Dec 4, 2012, 2:31:10 PM12/4/12

to

On Tue, 04 Dec 2012 14:02:30 -0500, rickman <gnu...@gmail.com> wrote:

>On 12/3/2012 10:49 PM, josephkk wrote:
>> On Sun, 02 Dec 2012 22:46:28 -0500, rickman<gnu...@gmail.com> wrote:
>>
>>> On 12/1/2012 4:11 PM, Fred Abse wrote:
>>>> On Fri, 30 Nov 2012 16:05:33 -0800, Paul Hovnanian P.E. wrote:
>>>>
>>>>> 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 ;-(
>>>>
>>>
>>> Isn't StarOffice the same as OpenOffice?
>>
>> Not in the least, it is the remnants of WordPerfect, a spreadsheet (maybe
>> old Lotus 123), some Corel image applications and something else. Never
>> was open source. I used to have a copy.
>
>I guess I got them confused a long time ago. StarOffice is free, as in
>"free beer" though, right? Or maybe I've just plain got it mixed up
>entirely. I seem to recall having a free copy of StarOffice a long time
>ago.

The early ancestry is a little messy but by EOL, when Oracle killed it,
StarOffice was essentially the paid version of OpenOffice. WordPerfect
passed through Novel, ended up with Corel, and is still around. The
spreadsheet app in the suite is Quattro Pro. The original Quattro was a
DOS app by Borland, launched shortly after Lotus 1-2-3 came out; thus
the name.

josephkk

unread,

Dec 5, 2012, 4:42:20 AM12/5/12

to

On Tue, 04 Dec 2012 14:02:30 -0500, rickman <gnu...@gmail.com> wrote:

>On 12/3/2012 10:49 PM, josephkk wrote:
>> On Sun, 02 Dec 2012 22:46:28 -0500, rickman<gnu...@gmail.com> wrote:
>>
>>> On 12/1/2012 4:11 PM, Fred Abse wrote:
>>>> On Fri, 30 Nov 2012 16:05:33 -0800, Paul Hovnanian P.E. wrote:
>>>>
>>>>> 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 ;-(
>>>>
>>>
>>> Isn't StarOffice the same as OpenOffice?
>>
>> Not in the least, it is the remnants of WordPerfect, a spreadsheet (maybe
>> old Lotus 123), some Corel image applications and something else. Never
>> was open source. I used to have a copy.
>
>I guess I got them confused a long time ago. StarOffice is free, as in
>"free beer" though, right? Or maybe I've just plain got it mixed up
>entirely. I seem to recall having a free copy of StarOffice a long time
>ago.

Though i never saw one that does not mean that no such thing existed. You
might have obtained a free (pirated) copy, StarOffice never was as anal as
M$.

josephkk

unread,

Dec 5, 2012, 4:56:58 AM12/5/12

to

Not sure what you are talking about, perhaps SunOffice? OpenOffice never
had anything to do with StarOffice AFAIK.

>
>WordPerfect got bought by Corel, and is still available AFAIK. Not the
>same product at all.

Actually the purchase chain is rather more twisted, i seem to recall that
Borland was in there somewhere. Corel was about the fourth owner of WP
and second owner of the suite.

See:

http://en.wikipedia.org/wiki/WordPerfect

josephkk

unread,

Dec 5, 2012, 5:02:14 AM12/5/12

to

On Tue, 04 Dec 2012 14:31:10 -0500, Rich Webb <bbe...@mapson.nozirev.ten>
wrote:

No, the twain (StarOffice - OpenOffice) never met.

See my other recent response.

?-)

Phil Hobbs

unread,

Dec 5, 2012, 9:13:09 AM12/5/12

to

Read the Wikipedia page on StarOffice. It has the whole story.

Cheers

Phil Hobbs

josephkk

unread,

Dec 6, 2012, 1:13:05 AM12/6/12

to

On Wed, 05 Dec 2012 09:13:09 -0500, Phil Hobbs

Hmmm. I wish i really thought it is accurate, it is wikipedia after all.

?-)

ChairmanOfTheBored

unread,

Dec 6, 2012, 8:37:44 AM12/6/12

to

On Wed, 05 Dec 2012 09:13:09 -0500, Phil Hobbs
<pcdhSpamM...@electrooptical.net> wrote:

>
>Read the Wikipedia page on StarOffice. It has the whole story.
>
>Cheers
>
>Phil Hobbs

I bought it a year before they started giving it away.
$79 down the drain, because it is one piece of stock that pays out
nothing.

John Devereux

unread,

Dec 7, 2012, 4:52:38 AM12/7/12

to

Well, the debian openoffice binary is still called soffice :)

--

John Devereux

Jim Thompson

unread,

Dec 7, 2012, 11:14:21 AM12/7/12

to

On Tue, 27 Nov 2012 09:28:02 -0500, rickman <gnu...@gmail.com> wrote:

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