Issue 5930

Summary: Engineering Notation
Product: Calc Reporter: brusque <brusque>
Component: formattingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: cbell44, edgardo, frank.loehmann, irne.barnard, issues, jumbo4444, kami911, kschenk, mike, ooo, orcmid, pagalmes.lists, rb.henschel, shammond, thomas.lendo, Thomas, vulcain
Version: 680m85   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
lets the user format numerical values...
lets the user format numerical values...
Macro to convert to Eng / Si notation
Macro to convert to Eng / Si notation (revision 2)
Custom (Engineering) Notation in MS Excel 2003
Engineering Notation in Gnome Calculator
Spreadsheet with conversion functions - enable macros to see
Python code runable file
Python code runable displaying Engineering Notation in numeric format
Python runable file showing Alpha SI Engineering Notation
Excel 2007 Engineering Format Examples Using # Formatting
Excel output display of "excel_engineering_notation_examples.xlsx"
Gnumeric output display of "excel_engineering_notation_examples.xlsx"
Calc output display of "excel_engineering_notation_examples.xlsx"
Eng format using conditionals
Implementation with Conditional formatting
Engineering notation with conditional formatting or STYLE() function
Techinque to handle zero values and negative numbers
gnumeric v1.10.8 ui implementation of engineering notation format
Class for Engineering Notation, includes .h and .cpp none

Description brusque 2002-06-17 22:24:48 UTC
The spreadsheet application (Calc) don't support "engineering notation".

notation is similar to scientific notation (1.233E+4), but only puts expoents that's multiples 
of 3 (like xE-12, xE-9, xE-6, xE-3, xE+0, xE+3, xE+6, xE+9 and so on) so that 1.233E+4 would read as 
12.33E+3 (12.33 kilo).
Comment 1 frank 2002-06-18 06:52:16 UTC
Hi Falko,

1 4 u

Comment 2 falko.tesch 2003-10-08 11:41:14 UTC
notation is similar to scientific notation (1.233E+4), but only puts
expoents that's multiples of 3 (like xE-12, xE-9, xE-6, xE-3, xE+0,
xE+3, xE+6, xE+9 and so on) so that 1.233E+4 would read as 12.33E+3
(12.33 kilo)
Note: Supported by Excel 2003
Comment 3 bettina.haberer 2003-12-05 16:11:39 UTC
Hello Niklas, please give approval for this evaluated OO.o 2.0 flagged
If you confirm with the target OO.o 2.0, then please keep it on your
owner (or the owner of the concerning developer) for implementation.
In case you want this issue for 'OOo Later', then please reset the
target milestone. If you decline the issue finally, please set the
resolution to 'Wontfix' (but do not close). In case of 'OOo Later' or
'Wontfix' please reset it on Bettina's owner. Thank you.
Comment 4 frank 2004-01-14 14:16:20 UTC
*** Issue 24373 has been marked as a duplicate of this issue. ***
Comment 5 frank 2004-01-14 14:17:43 UTC
Comment from Issue 24373 :

In Calc, it would be exceedingly useful to add built-in SI (Système
Internationale) standard data formatting, which would look like follows:

15,227 would be formatted as 15.227k
732,001,000 would be formatted as 732.001M
0.000,029,301 would be formatted as 29.301μ

Comment 6 niklas.nebel 2004-01-26 11:40:51 UTC
This is not on the list of features that we're going to do for 2.0. We need to
focus on the enhancements from the PCD document.
Comment 7 frank 2004-03-01 10:12:29 UTC
*** Issue 25974 has been marked as a duplicate of this issue. ***
Comment 8 frank 2004-08-08 16:26:42 UTC
*** Issue 32688 has been marked as a duplicate of this issue. ***
Comment 9 ooo 2005-03-24 11:31:07 UTC
Just to mention that a competitive product enables use of engineering notation
by a special forn of the scientific number format. Instead of, for example,
0.00E+00 they use #.00E+00, the amount of # characters determines the power to
be used. The standard engineering format using powers of 3 and one decimal would
be ###.0E+00
Comment 10 niklas.nebel 2005-03-30 08:58:19 UTC
Actually the total number of integer digits, "#" and "0", is counted. "0.00E+00"
and "#.00E+00" are equivalent, and "###.0E+00", "##0.0E+00" and "000.0E+00" all
force the exponent to multiples of 3 (with the last one adding leading zeros -
after all, leading zeros without modifying the exponent are quite useless).
"##0.0E+0" is among the default formats in XLS file format.
Comment 11 ooo 2005-03-30 12:03:08 UTC
Then it is a hidden feature and the online help concerning scientific 
notations should be updated with this information. 
Comment 12 ooo 2005-03-30 12:06:34 UTC
Information about using engineering notation should be added to the file 
Comment 13 ecastro 2005-03-30 12:12:42 UTC
Dear nn and cs,
Comment 14 ecastro 2005-03-30 12:22:10 UTC
Dear nn and cs,
In NO way format like "###.0E+00" fixes  the exponent to multiples of 3. That
formats only control how many digits are displayed (either in mantissa or
What it is needed is a way to fix 2.4e-5 as 24.0e-6 or 0.024e-3 but no other
combinations (for instance 0.24e-4). In tehat example, even if the format is
0.0e+00, 2.4e-5 must be rendered as 24.0e-6. 
What matters is that exponent figures must be multiples of 3: 0,3,6,9,12,15,18
(not having three digits)

- Enrique Castro -
Comment 15 ooo 2005-03-30 12:30:34 UTC
Ups - just encountered that my test numbers just gave the right format but 
others doesn't. 
Please use this table to see if the right format is viewed: 
0.00001234         12.34e-6 
0.00012345        123.45e-6 
0.00123456          1.23e-3 
0.01234567         12.35e-3 
0.12345678        123.46e-3 
1.23456789          1.23e+0 
12.3456789         12.35e+0 
123.456789        123.46e+0 
1234.56789          1.23e+3 
12345.6789         12.35e+3 
123456.789        123,46e+3 
1234567.89          1.23e+6 
12345678.9         12.35e+6 
123456789         123.46e+6 
Comment 16 harald.schilly 2005-03-30 12:38:09 UTC
@cs: i've tried your numbers and my 1E-10 and *10 in every row, but there are
always exponents other than a multiple of 3. i've never seen them correctly.
may you attach an example document where you think it works? 
Comment 17 ooo 2005-03-30 13:35:08 UTC
Claus (cs),

This is not implemented so it isn't a hidden feature. What Niklas and I were
referring is the behavior in Excel (the competitive product).

@Enrique: yes, if implemented a ###.0E+00 _will_ fix the exponent to multiples of 3.

Comment 18 frank 2005-04-25 10:31:47 UTC
*** Issue 48026 has been marked as a duplicate of this issue. ***
Comment 19 norbert2 2005-11-24 14:22:20 UTC
Could this issue be targeted to OOo 2.1? I'm really missing engineering and SI
notation in Calc. I think this issue is worth a P3 (not P4), at least.
Comment 20 bobharvey 2006-03-16 09:52:56 UTC
I'd like to add my voice to those who think this is important.
It is dissapointing to see it targetted to "OOo Later"
Comment 21 brusque 2006-03-16 10:50:06 UTC
Hello  bobharvey, norbert2, cs, ecastro and others,

    as the original poster of this issue I would like to thank you for the
attention given.

    I'm an electrical engineer and just want to add that engineers are *very*
used to see large or small numbers with expoents multiples of 3. For example,
very common capacitors values 100nF (100E-9 farads) and 47uF (47E-6 farads).
Some very common resistors values are 10kohm (10E3 ohms) and 1Mohm (1E6 ohms)
and MOSFETS (something similar to a transistor) have Rdson (resistance when on)
values like 12.6mohm (12.6E-3 ohms).

    Civil and mechanical engineers talk about 56MN (56E6 newtons) and not
5.6E7N, for example. It's very rare somone to talk about "resistance of 1.0E4 ohms".

    Given that, I also want to reinforce norbert2 opinion that this should have
a higher priority.

    Best regards,

Comment 22 mestech 2006-03-17 16:45:24 UTC
In my research, everything is in SI units.  I have been constantly converting to
the necessary units.  It would be great to see speeds in the correct format.

Also the recommendation for the proper SI prefix to be displayd as an option.
Comment 23 paulwolstenholme 2006-08-22 23:07:30 UTC
1) I've used this feature in ms excel 95 and probably even earlier versions. 
OOo is really deficient in letting this one slip to such low priority.  Please
raise the urgency!
2) The competing product suppresses leading digits when rounding to scientific
notation and this isn't always the nicest way to show things.  What excel will
NOT let you do is ask for engineering notation with a specified number of
significant digits.  For example, instead of seeing 1.2E3, 12.3E3, 123.4E3,
1.2E6 it is usually better to see 1.23E3, 12.3E3, 123.E3, 1.23E6 since this
maintains the same accuracy throughout, i.e. 3 significant digits in this
example - which is what we engineers really need.
3) The competing product does not allow suppression of the redundant trailing
E+0 or E00 or whatever.  This would be really nice.
4) It would be great to have a 'general engineering' format, similar to the
excel's general format but which switches to engineering format for numbers
between -1.0 and +1.0, numbers -1E3 and under and numbers 1E3 and greater. Note
that excel's general format already shows small numbers (below about 1E-5) in
scientific format instead of just 0 as in OOo 2.02.
Comment 24 mlisowsk 2006-09-12 11:20:51 UTC
This issue is now sitting unresolved since FOUR years! Is anybody ever going to 
work on it?

Please keep in mind that for non-programmers the display of a number like 1E-7 
is extremely unfamiliar. Displaying it like this makes spreadsheets unreadable 
to the general public.

OK, after this small rant I have a few suggestions:

1) Display floating point numbers in correct scientific format:
That is 1E-7 should be displayed as 1*10^-7  (of course without hat and with the 
exponent "-7" put as superscript and the asterisk being actually displayed as 
center dot). Like in every text book.
We could reserve the lower case "e" format code for this: "0,00e+0"

2) For the scientific format there should be an alternative to display with 
exponents as multiple of 3 and (optionally) with the exponent being shown as SI 
unit prefix "E-3" shown as "m" for milli, "E-9" shown as "n" for nano, etc. 
Combining this with a text format code would naturally result in a correct 
display of physical units.

Example: With a format code of "0,0S \m" the number 1E-8 would be displayed as 
"10.0 nm". Here I invented the format code "S" for this purpose.
Comment 25 frank 2006-10-25 12:30:24 UTC
*** Issue 70834 has been marked as a duplicate of this issue. ***
Comment 26 nick_ee 2006-11-17 11:55:03 UTC
Further to my suggestion that Engineering format be supported in the OO spread sheet, I found this 
formatting code on the web - consider incorporating it in some form....

def number_to_engineering(value, precision=3)
  expof10 = ((Math.log10(value)/3.0).floor)*3
  value *= 10**(-expof10)
    when value>=1000.0 : 
      value /= 1000.0
      expof10 +=3 
    when value>=100.0 :  precision -= 2 
    when value>=10.0 :  precision -= 1 
  "%.*fe%d" % [precision-1, value, expof10]

number_to_engineering(15000) => "15e3"

to ruby formatting number by Kevin on Tue Aug 23 07:40:20 GMT 2005

The idea of SI support is one I did not consider, but boy that would be good too.
So the selection entry sould be 'SI'. Simple, great.
Comment 27 mithro 2006-12-19 05:13:12 UTC
This issue is so important for me that I've ended up going back to Microsoft Excel.

I would really love to use OpenOffice for everything, would creating a bounty
for this item help it get added sooner?
Comment 28 jenf 2007-07-05 03:29:21 UTC
this is really an enormous problem for engineers and technical people
altogether. It is absolutly common, also in the computer world to name 1000,000
Byte = 1 MB. This is one thing that holds me back from deinstalling Excel.
Comment 29 bettina.haberer 2007-09-26 15:56:55 UTC
Hi Niklas, these RFEs are in your ownership.
Comment 30 petitjj 2007-10-16 21:49:59 UTC

One more redundant comment here: I totally agree that engineering notation would
be a very very appreciated feature for all scientists. Long list of numbers are
useless to us unless they are properly formatted (e.g. 10n instead of 1e-8!!)

I would be happy to contribute, if there are 'low enough' level functions to
develop in C (i.e. convert eng to sci and opposite), but not an expert in Oo
architecture and stuff.

Anyone started working on that / willing to start ?!


Comment 31 mlooo1 2007-10-23 14:00:57 UTC
Should we be opening an issue for Standardization "Engineering Notation" by ISO ;-)
Comment 32 spiritof76 2007-11-24 12:56:47 UTC
This issue has existed a long time, those of us who work with scientific or
engineering numbers find this a critical issue. This has existed for a long time
and deserves more priority.
Comment 33 jhhsj 2007-12-11 21:23:12 UTC
The custom format for the competitive product that produces a useful engineering
notation is: ###.000E+00

I would be happy with the results that are produced by that format:

  1.000 ->   1.000E+00
  0.100 -> 100.000E-03
  0.010 ->  10.000E-03
  0.001 ->   1.000E-03
  10.00 ->  10.000E+00
  100.0 -> 100.000E+00
  1000. ->   1.000E+03

Although I would prefer the mmmE+ee form where mmm is from 1 to 3 digits and ee
is the power of ten as a number divisible by 3.  I would call this "Engineering"
in the category listing, just like 1E+06 is called "Scientific".

An interesting Special format could interpret those into SI-named units, as in
100p, or 60K, etc.  That would be even better if the actual engineering unit
suffix could be included in the custom format definition, as in 800MHz, where
the 800M is from the numerical value (8E+08), and the Hz is the added suffix.

Given that I have wanted this since I first used spreadsheets I would give this
the highest priority (i.e fixing the custom code that is shown to work in E****
and make it work in Scalc) and would rate the others as nice to have.

Comment 34 petitjj 2008-02-29 19:19:43 UTC

Gnumeric supports it, I just found :)

Can we hope that the code would be inserted in O.O. soon?

Thanks for your great work, O.O. people! I really enjoy the product!!!
Comment 35 petitjj 2008-03-17 13:06:19 UTC
Created attachment 52154 [details]
lets the user format numerical values...
Comment 36 petitjj 2008-03-17 13:06:36 UTC
Created attachment 52155 [details]
lets the user format numerical values...
Comment 37 petitjj 2008-03-17 13:08:33 UTC
Hi all!

I'm attaching a very simple demo spreadsheet that shows how you can use vlookup
to apply a 'unit prefix' to a list of numbers.

I hope it will be useful for you.

You'll notice that it's quite convenient to have the same prefix for a whole
column. It makes it simpler to compare different values. 

Conclusion, the best would be to be able to choose between 

- normal engineering notation (as described above, noted 1. 10^3)
- eng. Notation + prefix  ( 1k, 1M, 1G, ...), and let oo chose the prefix
- eng. Notation + prefix, but let the user chose the prefix  (like in the demo

Comment 38 mestech 2008-03-17 18:32:55 UTC
petitjj, thanks for the idea.  It is a good move in the right direction.

I do hope that the Engineering Notation does make it into OOo sooner than later.
 Your demo is a start but would take to much programming on my data.  I need to
know the differences and I have measurements that go from 0 to 10^6 and back. 
Some of the spreadsheets have 60,000+ elements.

I will have to look at it further.
Comment 39 petitjj 2008-03-17 22:22:06 UTC
dBs are what you need, I guess, if you have to handle such large scales... ;)

For the spreadsheet bit, try gnumeric, it looks really powerful (lightweight and
handles large amounts of data).

But at this point (60k lines) you might also want to pre-process the data with
python or friends... generate a digest which you'll display in OO.

I hope it helps. 
Comment 40 mestech 2008-03-18 15:54:56 UTC
Pre-processing is already done.  The initial data is in the +200K lines.  The
issue with pre-processing is that no averaging or loss of data resolution can
occur.  Sudden transitions are very important to the analysis as well as average

I am hoping for this enhancement to come through sooner than later.

The engineering notation would be the icing on the cake but the vlookup
suggestion  is something I am looking at as an option to see if I can modify it
for my needs.
Comment 41 jbasc19921jbkas 2008-03-20 20:23:10 UTC
Love the idea of having an option of showing SI abbreviation rather than the

User should be able to select also between a fixed number of significant
figures, or a fixed number of decimal places in the significand (i.e. mantissa).
There should be an option also of not displaying a redundant E0.
Comment 42 swingkyd 2008-03-28 16:58:42 UTC
I was thinking, maybe an appropriate strategy in getting this implemented is to
have OASIS OpenDocument specify engineering notation as part of its standard.
That way, all vendors who wish to support OpenDocument would be required to
provide it.
Any thoughts on how to get this started?
Comment 43 mestech 2008-03-28 21:07:44 UTC
swingkyd, good suggestion.

Take a look at and see if the procedure is listed

Probably won't show up for a few years, much to late for many.
Comment 44 frank.loehmann 2008-05-22 09:31:19 UTC
This issue is important and listed on the quarterly review for Calc:
Therefore adjusting target to 3.x.
Comment 45 mestech 2008-05-23 16:15:11 UTC
Now this is making me happy.  No need to write custom cell formats to display
proper scientific data.

Comment 46 ka9q 2009-01-14 05:15:36 UTC
I really want this feature too. It would greatly improve usability for a lot of
my work.

As a much more ambitious feature it would someday be nice to tag numbers with
physical units, allow calculations on them while detecting any unit conflicts,
and display values as canonical SI units. I could enter 1 kg, multiply it by 1
m/sec^2, and the output would appear as 1 newton.

Comment 47 kami911 2009-01-14 09:58:15 UTC
@kohei: Hey Master, have you got time to implement this?
Comment 48 k_ivanov 2009-02-22 10:50:39 UTC
It is really strange why one of the most voted issues is not solved after more 
than 6 years! Looks like developers have different priorities. But to have 
customers need to hear the voice of the customers helps to have customers. 
Competitors already have make possible the engineering notation.
So herewith I would like to add my vote for this issue.

This issue becomes compatibility issue. 
Please possibly consider sooner implementation of engineering notation (SI) in
Comment 49 k_ivanov 2009-02-22 10:51:12 UTC
It is really strange why one of the most voted issues is not solved after more 
than 6 years! Looks like developers have different priorities. But to have 
customers need to hear the voice of the customers - helps to have customers. 
Competitors already have make possible the engineering notation.
So herewith I would like to add my vote for this issue.

This issue becomes compatibility issue. 
Please possibly consider sooner implementation of engineering notation (SI) in
Comment 50 ofirk 2009-05-17 20:26:04 UTC
Please implement this feature.

It will really help lots of people who study engineering and physics.
Comment 51 ofirk 2009-05-17 20:26:56 UTC
Please implement this feature.

It will really help lots of people who study engineering and physics.
Comment 52 spiritof76 2009-06-12 20:59:56 UTC
It is only ranked a 4 priority. This issue really does limit usability in an
otherwise great spreadsheet program. Its been 7 years and we are still waiting..
And I'm still having to use Excel for my engineering calculations
Comment 53 jeffbren 2009-09-08 19:48:16 UTC
I'm relatively new to OOcalc. It is now distributed with our engineering system
which is based on Linux. This is really unfortunate. As an electrical engineer I
MUST be able to format as requested. I am looking at a spreadsheet with over
12000 entries of time stamps. My mind is trained to look at milli, micro, nano,
femto, etc. By not having this capability you're not only causing a group of
possible supporters to discount the Calc but also working to keep them on Windows.

By the way, I know why there are so few votes for this request. As soon as I
found out that the feature didn't exist I went back to Excel. It took some
effort to find the issue documented, create a login and add a vote. The vast
majority of new users would not take the trouble to vote. They would just walk
away from the product.

Otherwise, the tool seems rather impressive and the developers should be proud.
It is really unfortunate that such a small issue can make it useless for my
Comment 54 lars.nooden 2009-09-08 20:51:24 UTC
What is needed to make this one valuable change go forward?
Comment 55 alastair_paton 2009-09-08 23:55:17 UTC

I too would like engineering notation.

To answer larsnooden's question below I believe QA should conduct an internal 
reassessment of this issue.

To help here I have reviewed the on-line history, summarised the issues, and offered some 
recommendations for consideration by My structure includes:
1. Summary (this section);
2. Way Ahead, including:
A. Separate the Two Issues,
B. Reassess Priority of the Two Issues,
C. Consider Dependancy of these Two Issues, and
D. Review Other Issues in the Database for Dependancy; and
3. Detailed Recommendations.

Back in 2004 two separate requirements were blurred together within this issue and I believe that 
blurring has hindered engineering notation in gaining appropriate implementation priority.

The two [blurred] issues are:

A. Engineering Notation, and

B. SI Prefixes.


The requirement for engineering notation was well articulated on 17 Jun 02 by the initiating post for 
this this issue [5930, "Engineering Notation"].

The only thing I would add is "Engineering notation is a version of scientific notation" [Ref: Wikipedia 
article on Engineering Notation ] as this may help in 
the implementation of engineering notation.


On 14 Jan 04 [24373, "Engineering and SI/MKSA notation support for Calc"] was raised, and 9 hours 
later it was deemed to be a duplicate of [5930, "Engineering Notation"].

I disagree with this assessment as they are are not the same issue, nor a double of the same issue.

[24373, "Engineering and SI/MKSA notation support for Calc"] does describe a requirement, and that is 
for SI Prefix, viz, "An SI prefix is a name or associated symbol that precedes a basic unit of measure" 
[Ref: Wikipedia article on SI Prefix ].


A. Separate the Two Issues

First of all, the two issues need to be separated so they be considered separately, and I would consider 
Engineering Notation ahead of SI Prefixes.

B. Reassess Priority of the Two Issues

The currently assigned priority of P4 for [5930, "Engineering Notation"] is too low.

"It is a non-trivial problem which affects a noticeable number of users" [from P3 
definition at] => the priority 
for [5930, "Engineering Notation"] should be raised to P3.

To substantiate the number of users: see the below posts; Sun 17 May 09 ofirk pointed out that "It will 
really help lots of people who study engineering and physics" (I totally agree with this comment); and, 
on 22 Feb 09 k_ivanov pointed out that [5930, "Engineering Notation"] is "one of the most voted issues".

Also, all the professional engineers I know use engineering notation when doing their calculations, and 
only at the end does one consider changing the SI Prefix when presenting the result => greater need 
(and priority) for engineering notation over SI prefix.

C. Consider Dependancy of these Two Issues

Once [5930, "Engineering Notation"] has been implemented, then I believe [24373, "Engineering and 
SI/MKSA notation support for Calc"] can be implemented => [24373, "Engineering and SI/MKSA 
notation support for Calc"] is dependant upon [5930, "Engineering Notation"].

D. Review Other Issues in the Database for Dependancy

I reviewed the other issues deemed to be duplicates and I believe:

(1) Issue [25974, "engineering number-format"] raised 28 Feb 04 is a mix of both [5930, "Engineering 
Notation"] and [24373, "Engineering and SI/MKSA notation support for Calc"] and should remain 

(2) Issue [32688, "Scientific cell format to snap to common multiples"] raised 6 Aug 04 is a duplicate, 
but it is a duplicate of Issue [25974, "engineering number-format"], not Issue [24373, "Engineering and 
SI/MKSA notation support for Calc"].

(3) The dependancy tree for Issue [32688, "Scientific cell format to snap to common multiples"] 
depends upon itself.


Recommend that:

A. Status of Issue [24373, "Engineering and SI/MKSA notation support for Calc"] be changed from 

B. Priority of Issue [24373, "Engineering and SI/MKSA notation support for Calc"] be assigned P4,

C. Issue [24373, "Engineering and SI/MKSA notation support for Calc"] be made dependant upon Issue 
[5930, "Engineering Notation"],

D. Name of Issue [24373, "Engineering and SI/MKSA notation support for Calc"] be changed to [24373, 
"SI Prefix"],

E. Priority of Issue [5930, "Engineering Notation"] be changed to P3 (at a minimum), and

F. Dependancy tree entry for [32688, "Scientific cell format to snap to common multiples"] which 
currently points to itself be removed.
Comment 56 irneb 2009-09-09 10:42:18 UTC
Agree wholeheartedly with (some of) alistair's comments.

Eng and SI are NOT the same thing. They are only related because they use the
same multiples (in most cases). They are definitely NOT duplicates!

SI allows for other exponents as well:
E.g. Hecto (h prefix) is E+02; Deca (da) = E+01; Deci (d) = E-01; Centi (c) =
E-02; Only for the rest (up / down) the 3 digit multiple applies.

See the WikiPedia page ( It may be
beneficial for implementation to have the SI issue as dependent on the Eng
issue, simply because part of it would become easier to implement. But these 4
situations then need to become exceptions, maybe as an option since they're not
always used in all industries. E.g. architecture usually uses millimeters, but
sometimes centimeters and / or decimeters depending on location.

BTW, the SI prefixes are already used in the COVERT_ADD function. It would be
nice to also then be able to display it!
Comment 57 nick_ee 2009-09-09 15:13:36 UTC
Last spring I tried an OO extension called 'measurement', that purported to do 
some scientific formatting. Unfortunately the Java based extension and 
compression scheme conflict with my Mac's default handling of compressed files 
and it would not install. The production of an extension to do the numerical 
formatting seems easier than trying to decipher the complete OO program build. 

More info:

Comment 58 nick_ee 2009-09-09 15:15:50 UTC
Last spring I tried an OO extension called 'measurement', that purported to do 
some scientific formatting. Unfortunately the Java based extension and 
compression scheme conflict with my Mac's default handling of compressed files 
and it would not install. The production of an extension to do the numerical 
formatting seems easier than trying to decipher the complete OO program build. 

More info:

Comment 59 irneb 2009-09-14 15:18:43 UTC
Created attachment 64755 [details]
Macro to convert to Eng / Si notation
Comment 60 irneb 2009-09-14 15:24:25 UTC
OK, I've just attached a spreadsheet with a sBasic macro for converting a number
to Eng notation or Si notation. Unfortunately both cases are text, so direct
calculations would not work. The Eng notation can easily be reverted back to
normal Scientific notation through the VALUE formula. But, the Si notation would
require a further macro to convert back ... should not be too difficult ...
hopefully ... will come back when I've got some time to fix that up.

Please note, this is a rough testing program. There's possibly quite a few
errors, and I haven't even looked at enabling enforcement of a minimum number of
digits pre- / post the decimal point.
Comment 61 irneb 2009-09-15 09:24:51 UTC
Created attachment 64763 [details]
Macro to convert to Eng / Si notation (revision 2)
Comment 62 irneb 2009-09-15 09:33:19 UTC
Modified version to allow specified precision of number's decimals as well as
the minimum number of digits for the exponent. Also added conversion from SI and
to SI names instead of abbreviations ... not really necessary, but it was so
easy after the SI notation.

I note one problem however: anything too small (less than yocto) simply gives a
0. If anyone knows how to get round that scenario, please let me know. Next step
would be to make this into an Add-On and optimize it a bit.

Then to figure out how to actually incorporate this format conversion into the
display format functions so the user doesn't need to convert to-and-from text
and numbers.
Comment 63 aoo-bugger 2009-09-15 13:37:05 UTC
hello irneb,
I appreciate your effort, however it seems that you slightly misunderstood this
Issue. This issue is _not_ about automatic detecting of numbers and to convert
them into SI _names_ (with which one cannot calculate).
I'm sure that when you carefully read the first post of this issue (again), you
will get the difference.

What you are currently working on is complete new feature, so I kindly advice
you to open a new issue (as a feature request) for this. Thanks.
Comment 64 irneb 2009-09-15 13:41:13 UTC
A similar (if not entirely duplicate) of this seems to be Issue 53587. Where that
differs from this issue is the 2nd request for Binary notation. See that issue
for the additional Conversion function to Binary notation (e.g. 1024 becomes 1.0Ki)
Comment 65 irneb 2009-09-15 13:46:36 UTC
Yes, what I'm working on is a work-around to the existing problem. With some
nested formulae it would now be possible to have Eng notation displayed while
still being able to calculate with the text. Not ideal, but at least it gets job

I have looked at changing the Cell Format functions, but that's beyond my
expertise. I've passed this along and hopefully someone could incorporate it (or
something similar) to get exactly what is requested here.
Comment 66 aoo-bugger 2009-09-15 14:20:04 UTC
Created attachment 64774 [details]
Custom (Engineering) Notation in MS Excel 2003
Comment 67 aoo-bugger 2009-09-15 14:41:05 UTC
I just uploaded an example screen shot of how the solution is in MS Excel 2003.
And thats also the way it is described in the first post and thus how I
understood this enhancement/issue so far.

After a closer look at the comment history, it seems that Frank (fst) was the
first who mixed this issue up, by wrongly marking Issue 24373 as a duplicate and
adding the comment here...

I would suggest to keep this enhancement as it was requested and to (re-)open
other feature request for your work.
Comment 68 cno 2009-09-15 14:54:40 UTC
@farno & irneb: I investigate (try to discuss) the IMO useful suggestion from
alastair paton in #desc56, who indeed suggest to reopen issue 24373 etc.

To be continued ..
Comment 69 irneb 2009-09-15 15:03:23 UTC
Yes well, I've also noted that Issue 24373 is not a duplicate of this since SI
notation is not the same as Engineering notation. Both issues however would want
the Cell Format to work instead of using conversion functions. The code I've
attached is simply my way of getting this functionality available in OOoCalc.

Unfortunately not with Cell Formatting but at least the result is the same
(sort-of). It's not as easy to use as Cell Formatting would be, I'm the first to
admit that, but the displayed "value" at least shows as it should. It is by *no
means* a solution to this problem! It's a work-around so those who cannot wait,
for some developer to one day actually do something about it, could at least get
their spreadsheets to a working format.
Comment 70 thomasb12 2010-05-21 23:50:42 UTC
I find it hard to believe that no one is taking this deficiency seriously!  It
is most important to engineers.
How many years will it be before someone acts on this?
This is the 21st century OOo should join it.
Comment 71 lars.nooden 2010-05-27 10:02:53 UTC
Adding engineering may be an ideal task for the student internship:
Comment 72 credmann 2010-05-27 14:38:34 UTC
Created attachment 69662 [details]
Engineering Notation in Gnome Calculator
Comment 73 thomasb12 2010-05-28 02:44:13 UTC
I found this snippet of code.  Can someone use it to create the necessary macro?

import math

for exponent in xrange(-10, 11):
flt = 1.23 * math.pow(10, exponent)
l = math.log10(flt)
if l < 0:
l = l - 3
p3 = int(l / 3) * 3
multiplier = flt / pow(10, p3)
print '%e => %fe%d' % (flt, multiplier, p3)

It was on this page:

Comment 74 thomasb12 2010-07-03 07:55:59 UTC
I have two files that I need to convert to addins or addons or whatever.  One
constrains all exponents to be divisible by 3, except for 0, of course.  The
other works much the same except it converts to alpha character values, i.e., m,
k, M, T, p, etc, in the place of exponents.
If someone can help me put a wrapper on this to get OOo Calc to recognize it, we
can all enjoy the results and be one up on M$ Excel.
Thank you!

Comment 75 irneb 2010-07-05 07:54:55 UTC
Could you attach the files here? I'd be interested to see how you did this as
mine is simply using a conversion formula (to-and-from) text. If you've got it
working with the format idea itself, this may be a step in the right direction!
Comment 76 thomasb12 2010-07-05 19:44:59 UTC

def eng (F = 0, fmt = "%f"):
 Formats a floating point number (F) according to the format
 provided (fmt).  Tries to use engineering notation (i.e. the
 exponent is in powers of three).

 Dean Provins, June, 2010
 LExp = ['0','1','2','m',' ',' ','%mu',' ',' ','n',' ',' ','p']
 HExp = ['0',' ',' ','k',' ',' ','M',' ',' ','G',' ',' ','T']
 f = abs (F)
 n = 0
 s = +1
 if F < 0:
  s = -1

 if f != 0:
   if f >= 1.:
     while (f >= 100):
       f /= 10.
       n += 1

     f *= 10.
     n -= 1

     while (n % 3):
       n += 1
       f /= 10.
     while (f < 99):
       f *= 10.
       n -= 1

     while (n % 3):
       n += 1
       f /= 10.

     #  Uncomment these if you want a leading '0.'
     #f /= 10.
     #n += 1

 #S = fmt % (s * f, n)  # store this result in a cell
 if F < 1:
    if s > 0:
      return fmt % (f) + LExp[-s * n] # return the formatted string to store in
a cell
      return fmt % (s * f) + LExp[s * n]
  return fmt % (s * f) + HExp[n * s]
#       ---------end of the function ---------

if __name__ == "__main__":

#       Some tests...  Try them by running "python"

  print "some tests"
  print "----------"
  print "0:", eng ()

  print "1.:", eng (1.)
  print "1.23:", eng (1.23)
  print "123:", eng (123)
  print "1234.567:", eng (1234.567)

  print "11:", eng (11)
  print "* * * Less than One Positive * * * "
  print "0.4:", eng (0.4)
  print "0.0004:", eng (0.0004)
  print "0.0000004:", eng (0.0000004)
  print "0.004:", eng (0.004)


#  print "0.001234567E-7:", eng (0.001234567E-7)
  print "6,000,000:", eng (6000000)
  print "6,000,000,000:", eng (6000000000)
  print "6,000,000,000,000:", eng (6000000000000)
  print "* * * * < 1,000, > 1 * * * * * * * * * * "
  print "111:", eng (111)
  print "222:", eng (222)
  print "333:", eng (333)
  print "444:", eng (444)
  print "555:", eng (555)
  print "666:", eng (666)
  print "777:", eng (777)
  print "888:", eng (888)
  print "999:", eng (999)
  print "* * * * * * Negatives! * * * * * * * * "
  print "-111:", eng (-111)
  print "-222:", eng (-222)
  print "-333:", eng (-333)
  print "-444:", eng (-444)
  print "-555:", eng (-555)
  print "-666:", eng (-666)
  print "-777:", eng (-777)
  print "-888:", eng (-888)
  print "-999:", eng (-999)
  print "* * * * *  Less than One Negative* * * "
  print "-0.4:", eng (-0.4)
  print "-0.0004:", eng (-0.0004)
  print "-0.0000004:", eng (-0.0000004)
  print "-0.0000000004:", eng (-0.0000000004)
  print "-0.004:", eng (-0.004)
  print "* * * * * * Just over One Thousand * * * * "
  print "1111:", eng (1111)
  print "2222:", eng (2222)
  print "3333:", eng (3333)
  print "4444:", eng (4444)
  print "5555:", eng (5555)
  print "6666:", eng (6666)
  print "7777:", eng (7777)
  print "8888:", eng (8888)
  print "9999:", eng (9999)
Comment 77 thomasb12 2010-07-05 19:48:20 UTC

def eng (F = 0, fmt = "%fe%d"):
 Formats a floating point number (F) according to the format
 provided (fmt).  Tries to use engineering notation (i.e. the
 exponent is in powers of three).

 Dean Provins, June, 2010
 f = abs (F)
 n = 0
 s = +1
 if F < 0:
  s = -1

 if f != 0:
   if f >= 1.:
     while (f >= 100):
       f /= 10.
       n += 1

     f *= 10.
     n -= 1

     while (n % 3):
       n += 1
       f /= 10.
     while (f < 99):
       f *= 10.
       n -= 1

     while (n % 3):
       n += 1
       f /= 10.

     #	Uncomment these if you want a leading '0.'
     #f /= 10.
     #n += 1

 #S = fmt % (s * f, n)	# store this result in a cell

 return fmt % (s * f, n) # return the formatted string to store in a cell

#	---------end of the function ---------

if __name__ == "__main__":

#	Some tests...  Try them by running "python"

  print	"some tests"
  print "----------"
  print "0:", eng ()

  print "1.:", eng (1.)
  print "1.23:", eng (1.23)
  print "123:", eng (123)
  print "1234.567:", eng (1234.567)

  print "11.:", eng (11.)

  print "0.4:", eng (0.4)

  print "0.004:", eng (0.004)
  print "0.04:", eng (-0.04)

  print "0.001234567E-7:", eng (0.001234567E-7)
  print "6,000,000:", eng (6000000)
  print "6,000,000,000:", eng (6000000000)
  print "6,000,000,000,000:", eng (6000000000000)
  print "* * * * < 1,000, > 1 * * * * * * * * * * "
  print "111:", eng (111)
  print "222:", eng (222)
  print "333:", eng (333)
  print "444:", eng (444)
  print "555:", eng (555)
  print "666:", eng (666)
  print "777:", eng (777)
  print "888:", eng (888)
  print "999:", eng (999)
  print "* * * * * * * * * * * * * * "
  print "-111:", eng (-111)
  print "-222:", eng (-222)
  print "-333:", eng (-333)
  print "-444:", eng (-444)
  print "-555:", eng (-555)
  print "-666:", eng (-666)
  print "-777:", eng (-777)
  print "-888:", eng (-888)
  print "-999:", eng (-999)
  print "* * * * * * * * * * * * * * "
  print "-0.4:", eng (-0.4)
  print "-0.0004:", eng (-0.0004)
  print "-0.0000004:", eng (-0.0000004)
  print "-0.004:", eng (-0.004)
  print "1111:", eng (1111)
  print "2222:", eng (2222)
  print "3333:", eng (3333)
  print "4444:", eng (4444)
  print "5555:", eng (5555)
  print "6666:", eng (6666)
  print "7777:", eng (7777)
  print "8888:", eng (8888)
  print "9999:", eng (9999)
Comment 78 nick_ee 2010-07-06 01:51:02 UTC
I'm not much of a programmer, but  I spent some time looking at the developer instructions for OO 
spreadsheet cell formatting. Cell formatting is a subset of the programming task. It appears that a 
formatting utility can be written and added to the existing list of formatting types. The various modulo 3 
algorithms presented here are okay, but the tough step is incorporating them into the source code for cell 
formatting. Sorry, but I didn't actually probe as far into the source as I'd need to to answer the specifics of 
how to do this.

Second, I agree that this request 5930 is two issues - numerical engineering format (modulo 3 exponents) 
and the scientific character nomenclature - the SI/MKSA units. Both can be handled with similar internal 
mechanisms, with different exponent representations - numerical or letter.
Comment 79 irneb 2010-07-06 06:34:55 UTC
Agreed. What I've seen here doesn't do anything more than what my original
custom function in Calc did: convert a number into a text representing the
engineering / SI notation. See the attached EngFormula.ODS file.

I've spoken to some developers and apparently it is possible to "add" a format
to the current ODS file only, not directly to the Calc application. For that it
has to become part of the source code to the program. Not simply an add-on. Thus
you'll have to load the add-on into each and every ODS file you open.

Now if some developer would please use one of these functions to add it into a
patch, that would be great. I don't care which, they all seem to work correctly.

AND they address BOTH Eng & SI ... so whether it's 2 issues or not is immaterial
when referring to a fix.
Comment 80 thomasb12 2010-07-06 08:44:42 UTC
I saw no way to attach a file that would survive the maillist.  I know this is
not a normal reply maillist, however, these comments are sent out to everyone
subscribed to this particular bug report.
It has been about 8 years since this was first requested and no one has even
looked at it.  There is no interest in doing this by the developers that I can
see, even though it is something that all engineers who employ a spreadsheet in
their work/research would gladly use.  It would also put OOo one up on Excel.
It is a shame that the developers do not care at all about this issue.
Both issues(numeric and alpha)!
Comment 81 irneb 2010-07-06 10:06:10 UTC
At the top of the web page
( you'll see the
attachments. After you've logged in there should be a link named "Create new
attachment" which should allow you to browse to a file for attaching.

I've been given links to the following after asking on the API forum:

It seems as if no-one is available for this fix. So if none of us can do it,
it's not going to be done. There seems to be some samples under the "Applying
Number Formats" sub-heading of the wiki link. In C++ though ... not my language
of choice, I last used it in the late 90's while studying. But I could try to
remember ;). Just don't have a lot of time myself.
Comment 82 irneb 2010-07-06 10:19:25 UTC
Created attachment 70413 [details]
Spreadsheet with conversion functions - enable macros to see
Comment 83 irneb 2010-07-06 11:50:51 UTC
To get this thing moving, I've started a wiki page under the UX (User
Experience) community.

Please register if you would like to contribute on the discussion / design of this.
Comment 84 lars.nooden 2010-08-01 10:50:16 UTC
If the problem stays constrained to the single issue reported above in the
initial problem report is is only a matter of  numerical engineering format
(modulo exponents) 

The solution is more than a printf() but should still qualify as low-hanging
fruit because scientific notation ( 1.23 E + 003 ) is already implemented.  
Comment 85 crugdeon 2010-08-01 11:07:22 UTC
Dear larsnooden, i wonder if you read the comments and made yourself familiar
with the subject at all. I am unable to replicate an engineering notation with
the number you provided. I am using Openoffice 3.2.1 build 9502. The number
"1230" with the Format "0,00E+000" which displays an exponent with a multiple of
3 as expected "1,23E+003" but choosing "12300" yields "1,23E+004" which is not a
multiple of 3.

I am currently unable to see how the current scientific notation solves the
clearly stated need for a notation, that will display the decimal exponent in a
multiple of 3 without requiring user activity by creating for example a macro to
alter the numeric display.

* Additional comments from brusque Thu Mar 16 10:50:06 
* Additional comments from jhhsj Tue Dec 11 21:23:12
* alastair_paton Tue Sep 8 22:55:17 +0000 2009 
Comment 86 thomasb12 2010-08-01 21:25:35 UTC
You seem to have missed entirely the point to Engineering Notation.  All
exponents must be constrained to those divisible by 3, whether positive or
negative.  Secondly, some engineers would prefer the letter designation (m, k,
M, p, G, etc.) to use when doing their calculations.
Making the letter designations available would certainly make Calc more
attractive to many engineers than Excel.
Offering a better product than M$ is not something we should sneer at.
Thank you!
Comment 87 paulwolstenholme 2010-08-01 22:04:09 UTC
I agree with Lars' comment that the problem as originally described (displaying
the exponent as a multiple of 3) is 'low hanging fruit' and ought to be easy to

Lars did NOT say that the problem was already solved so I do not think there is
any evidence to suggest that he 'missed the point' at all.  As far as I am
concerned he is right onto it!

I do not believe thomasb12's recent statement starting with "Secondly..." is a
useful posting in this issue as it is totally contrary to the detailed posting
of alastair_paton on Tue Sep 8 22:55:17 that clearly placed that additional
feature under issue 24373 which could be entitled 'nicer fruit that is higher up
the tree and harder to get to'.
Comment 88 alachabre 2010-08-01 23:08:55 UTC
There's little more to say than it is stunning that this simple yet essential
feature has lain on the table as an open and unresolved issue for over eight
years.  Are there no engineers at all in the Open Office organization?  Is there
anything I can do to help the issue along besides lamely complaining in this
forum?  I repeat, this is stunning, almost beyond belief.  Oh well, back to
working with the evil empire - this relationship with Open Office was short,
indeed; less than an hour.  Wow. <shakes head and walks away>
Comment 89 thomasb12 2010-09-07 06:56:03 UTC
Created attachment 71591 [details]
Python code runable file
Comment 90 thomasb12 2010-09-07 06:57:07 UTC
Created attachment 71592 [details]
Python code runable displaying Engineering Notation in numeric format
Comment 91 ke6pij 2010-09-28 03:03:40 UTC
There isn't a $20 scientific calculator that isn't able to display in
Engineering Notation, and yet this spreadsheet is still having discussions about
it. Engineers are the ones who can influence others to use Open Office, yet why
would the promote a tool that they can't use because it doesn't provide a basic
format in their daily use?

I like Open Office, but the spreadsheet is useless to me in over half of the
tasks I would need to use it in. Believe me, I do not want to use the "other"
product offering, but it is a necessary evil.

It's really just putting the numbers so they have exponents in multiples of 3
and from one to three digits to the left of decimal point, that's it. Forget the
G, M, k, m, n, u for now... that's fluff. Just get the exponents to display in
engineering notation and many people will be happy.
Comment 92 mestech 2010-10-08 16:27:44 UTC
ke6pij put it into basic terms.  This is not a new issue for those in an
engineering field.  My first real (more than basic) calculator could do
engineering notation in the 70's.  I cannot think of a calculator I would
purchase today that won't support this.

I will be watching what happens with the DocumentFoundation's fork and see if
they respond to issues quicker.  I will be moving to the first one that fixes
this issue.

OOo, with the row limit being fixed would now be useful to start processing our
trial data, if we had engineering notation.
Comment 93 eadthem 2010-10-10 14:05:38 UTC
its very simple, the first spreadsheet to support T G M K m u n p f will be the
first one i use after open office.  I know that adding this support will cross
with just entering text but theres a simple fix for that.

if the SI letter is right after the number, it will be taken as SI, if there is
a space it will be taken as text.

.1uF  .0000001
.1nH  .0000000001
.01p  .00000000000001
1 The big black ship     1 (the space makes T text and not Terra
1The big black ship    1,000,000,000,000  (no space makes the t Terra

support for key words would be nice as well Yotta, Zetta, Exa, Peta, Terra,
Giga, Mega, Kilo, Hecto, Deca, Deci, Centa, Mili, Micro, Nano, Pico, Femto,
Atto, Zepto, Yocto

Aa might as well support other things such as deca and centa.

A option to default to SI or to any other formatting of cells is also required,
rather than having to set the format every time a sheet or new workbook is created.

This is my hope for the next version of open office.
Comment 94 thomasb12 2010-10-20 09:02:34 UTC
Created attachment 72110 [details]
Python runable file showing Alpha SI Engineering Notation
Comment 95 lml 2010-10-20 10:11:27 UTC
For eadthem's comment:
"if the SI letter is right after the number, it will be taken as SI, if there is
a space it will be taken as text."

I don't think it is a good idea to diferentiate on the space character. There
are languages where there is a (non-breaking) space between the number and the
SI letter.

I suggest the following way:
check the word following the number: If it is constructed from the word from the
SI prefix list (y,z,a,f,p,n,n,u,m,c,d,da,k,M,G,T,P,E,Z,Y) followed by a word
from a predefined (language-specific) list of known unit names
(m,kg,g,s,A,V,K,°C,mol,cd,Hz,N,Pa,J,W,C,F,T,H,ohm,...), then it is a number
using Engineering Notation. In all other cases ( and if there are additional
text after the prefix+unit?) the input is text.

The SI list a letter list, except the deca "da". It should be considered wether
to handle this exception or not. (I think it is not a big loss if we miss da and
use the one-letter prefixes only.)
There can be aliases, eg. for micro, "µ" and u is used. I don't know wheter
these prefixes are international symbols, but a think it is, so it can be a fix

For the unit name list the using of aliases should be considered, eg. "m" and
"meter" and "metre" are the same thing, as the "ohm" and the greek Omega letter.
Unit names can be language-specific. In Hungary, for the unit "second" we use
not only the letter s, but also "mp" or "másodperc" (the Hungarian name of second).

So my suggestion is to recognise the one-letter SI prefix followed by an element
from the (language-specific) unit names list.
Comment 96 swingkyd 2010-10-20 16:24:36 UTC
I contend that SI notation is a separate issue from Engineering notation. The
dev's are clearly too busy to deal with this feature request so it's likely only
going to be fixed if we do the following:
1) split up the issue into two issues as previously suggested
2) somebody take on this issue by becoming a dev. 
Any takers?
Comment 97 eadthem 2010-10-21 00:07:40 UTC
lml: I figured there would be issues where .1 u is proper, but, how do you
handle the folowing cases.

.1uF or .1 uF  .1 u

my thougts are that ".1uF" and ".1 u"  would be valid as micro, i can't see how
.1 uf could be handled as a number with a type of farads without causing issues
for text users.   this work?
I think thats 1 of the major reasons for a lack of implementation.

I am a programmer and i have downloaded the source 2 times now meaning to fix
this(both si and eng) my self. I have not yet gotten to it, but, I think ile
spend the weekend on it.  To any DEV who may take this up, Go for it. I can be
reached on as eadthem.
Comment 98 irneb 2010-10-21 07:48:21 UTC
I think you're over-complicating the issue. Reverting the string back to a
number is not part of the formatting. Since nowhere does any of the format
functions need to do this. The number is stored internally as a number, not a
string - it's simply displayed as a formatted string. Other calculations are
done on the number, not the string.

The conversion back from a formatted string becomes irrelevant if we can get the
formatting to work properly. It may become a separate issue which could (and
should) be handled by a custom formula (as it is already). This may be useful in
situations where data is read in from a text file or such. So please open
another issue for this, so it can be fine-tuned on its own and maybe added as a
built-in formula.

About the SI and Eng being 2 separate issues, technically that is correct.
Practically, there's no difference. If the formatting can be made to work with
Eng, it would be a near instantaneous modification to generate the same thing
for SI. In both cases most of the hard-work is already done. All that needs to
happen is choosing the most efficient (and least re-write prone) algorithm of
those already available, then incorporating it into the formatting functions as
a patch to OOo - since an add-on is impossible for this.

The last bit is where I'm struggling, the documentation is extremely confusing
(at least to me) and there's no such examples (only examples of setting "custom"
formats which are already available in the format dialog - not creating a new
formatting type). If I can figure this out, or get someone to show me how / an
example I'd be willing to fix it in my "free" time.
Comment 99 othr 2010-10-21 08:00:41 UTC
I agree that the first priority is to be able to display a number with 
engineering notation, first E03, E06 etc., then secondly display a number with 
suffix milli, micro etc. 

Text recognition can come later. And a unit of measure suffix can also surely 
be added later - in Excel one can add text to the display of any number (a unit 
is the same regardless of the exponent).

Comment 100 Regina Henschel 2010-10-21 19:25:22 UTC
cc me
Comment 101 nick_ee 2010-10-21 20:39:51 UTC
I'm not an experienced programmer. I did look through the source, but was lost in trying to recompile 
the source on my computer. It appears the printout format is a set of procedures that can be extended 
with additional routines.

As for input formats, Open Office already can read in numbers with arbitrary number padding, including 
something like 32.456E003. This works just fine. 

SI units are a separate category of problem, requiring new input and output routines. Let's aim for the 
scientific output format first.


Comment 102 thomasb12 2010-11-07 00:22:24 UTC
Calc already has Scientific Numbering.  What is lacking is a way to constrain
the exponents to multiples of 3, (e.g., 9, 6, 3, 0, -3, -6, -9, ... etc).  I
agree that the Alpha version can wait on the appearance of the Engineering
Notation for multiples of 3.
This constraint to multiples of 3 is not currently possible except accidentally
when it just occurs due to the integer being limited to one digit.
Currently there is NO WAY to constrain the exponents to multiples of 3
The Python code has two parts, the first of which is used alter the display
values of the exponents, while the second displays the results.  This is
strictly to show how the code works.
Anyone who wants just the code version let me know.
Thank you!
Comment 103 thomasb12 2010-11-13 07:32:04 UTC
Will there come a day when someone will wake up and decide to do something about
this.  I was looking into adding it as an add-on, however, I do not have the
required SDK and since I am using a slightly older version the 3.2.1, I have no
access to the current SDK.
If someone out there can take the .py file and create an add-on please let me
know.  Thank you!
Comment 104 irneb 2010-11-15 06:38:05 UTC
The only "add-on" you can hope for is to have a conversion function as per
Thomas's PY code or my SBasic code. I.e. you end up with a formula to change the
number into a string - which means you can't directly calculate from it.

There's no way for an add-on to add something to the formatting functions. They
have to be compiled into OOo Calc itself. Thus it has to be a patch and a later
update. Or at least that's how I understand it, given that I've been looking for
this a while now and cannot find anything which shows how.
Comment 105 nick_ee 2010-11-16 09:10:41 UTC
I compared behavior of OO vs Excel cell scientific formats:  ###.00e+000  constrains Excel cell to have 
exponent as multiples of 3, with 1, 2, or 3 digits before decimal. Same format specification causes OO to 
fix 3 digits before the decimal, with varying exponent. Hypothesis: coding change required is to change 
from fixing the number of pre-decimal digits to making the exponent a multiple of the number of #'s 
before the '.'  This implies that no new formatting type is needed, only recognition of '#*.' pattern. IE Excel 
sees ##.00e+00 as fixing exponent to multiples of two. ####.00e+00 fixes exponent to multiples of 4 - 
i.e. the number of hash marks immediately preceding decimal point in specification.
Comment 106 thomasb12 2010-11-26 23:11:54 UTC
Yes, it could be done that way, however, no one cares to make the change. 
Absolutely no one amongst the devs could be bothered to make this change because
it is of no importance to them.
Since 2002 this "minor" feature request has been languishing for want of
attention from anyone in the dev community.
It would help to set Calc apart from Excel if it was done right.
It's a shame no one cares!!!
Comment 107 moseby 2010-11-29 01:22:42 UTC
Maybe the guys over at LibreOffice will be more receptive.
Comment 108 ofirk 2010-11-29 06:30:03 UTC
Or more specific at:
Comment 109 nick_ee 2010-11-29 12:20:34 UTC
I propose an additional variable in the cell formatting data structure, an integer modulo number identified 
as described in a previous posting, from the number of #'s immediately before the '.'. Perhaps the 
zforscan.cxx and zformat.cxx files are required to be modified. Not sure exactly where the cell format 
structure is stored. I've been poking around, but don't have a build system set up, so haven't been able to 
test it out.
Comment 110 niklas.nebel 2010-11-29 12:33:21 UTC
The first step should be to propose an extension of the ODF file format.

The code to actually output a number is the smallest part, but it wouldn't help
if the new format can't be loaded and saved.
Comment 111 irneb 2010-11-29 13:18:52 UTC
So the ODF spec needs some a new style type for the Si format. And for the
engineering there needs to be added a number:exponent-increment (or some such)
to the number:scientific-number style.

BTW, how are XLS's formats handled when they have the ###. format as per Excel's
"Engineering" format? It's in any case "wrong" the way OOo.Calc works at
present. A 0 in the custom format is supposed to mean there **HAS** to be a
digit in this spot. A # is supposed to mean, there **COULD** be a digit in this

But if your custom format is ###.00E+0 ... then absolutely ALL numbers fill ALL
3 the digits before the decimal. I.e. a value of 1 will format as 100.00E-2,
where logically it should have read 1.0E+0.

Even if you format it using ###, this is only remembered in the same session of
having the file open. If you close and re-open the file, the custom format
reverts to 000.00E+0 ... which logically is how it's formatting at the moment.
So at present it disregards the user's explicit format codes in totality -
nowhere in any of the XML files contained inside the ODS file is the custom
format code saved at all. No wonder this is such a "difficult" thing to accomplish.

It shows up an enormous hole in the ODF standard! Extreme difficulty in
extending styles.
Comment 112 nick_ee 2010-11-29 17:23:05 UTC
I hadn't thought about modifying the ODF data storage spec. It was my expectation that some 
combination of must show and can show data elements could imply engineering modulus, however a 
new data element for modulus of the exponent is a cleaner way to do it.

The expected behavior for ###. format is numbers in the range from 1. to 999.  Anything outside those 
ranges would cause the exponent to be renormalized (+/- 3) to scale the integer portion of the number 
back into range. (with ##. the exponent modulus would be by 2, and for ####. the modulus would be 
4, etc.)

Excel remembers the formatting as being 'custom' for each cell. I've written and reread data files to 
check this.

As for SI suffixes that will require more work to specify.
Comment 113 irneb 2010-11-30 05:58:48 UTC
This is how a format of ###.00E+00 is saved inside the styles.xml inside the ODS
<number:number-style style:name="N107">
	<number:scientific-number number:decimal-places="2"
number:min-integer-digits="3" number:min-exponent-digits="2"/>

And here's the spec:

It never saves the custom format string. It always translates it into values for
its properties. And seeing as the design of the properties don't allow for
exponent factors .... :(

As you can see the ### before the decimal "translates" incorrectly as:
number:min-integer-digits. Now I can understand 000 being minimum digits, but
not ###. Strictly speaking ### could format anything from a blank, through 0 to
999. 000 can only format from 000 to 999 ... noting else is possible. To have a
"true" engineering format the custom format string should actually be ##0.00E+0,
since you want at least one integer digit but a maximum of 3.

The SI format spec shouldn't be "difficult" to define. The "difficult" portion
is to get it into the spec, through the bureaucracy of the ODF council, and
finally into the programs which use it. The same actually applies to the
proposed change in the scientific format to accommodate engineering.

This is usually why other programs start branching into proprietary formats.
They want to introduce a new feature, but can't since the format (which they
have little control over) doesn't allow for it.
Comment 114 nick_ee 2010-11-30 07:50:48 UTC
imeb - you clarify a great deal with your post. Thanks for the documentation links.

To my understanding:
-Scientific and Engineering numbers always start out with a non-zero digit, except for zero. Hence they 
are normalized as 1..999, with 0 leading only when dealing with the quantity is 0, and an exponent is 
not used
-Excel only treats numbers as 'engineering' when the format contains exactly '#.' with a varying number 
of leading #'s. They don't seem to have a separate engineering format, per se.
-Your storage description leaves out the grouping data element in the Oasis spec: {The 
<number:scientific-number> element has the following attributes: number:decimal-places 19.345.4, 
number:grouping 19.350, number:min-exponent-digits 19.353 and number:min-integer-digits 
19.354.} which could be used in our favor without changing the storage format or specification.

Grouping is a boolean flag which means thousands separators, which Excel does not use when 
displaying Engineering exponents, since by definition you will never have enough leading digits to 
require the separator. I.e. in Excel 123456789 becomes 123.456789E+06 with ###.000000E+00 
formatting, 1234567890 would be 1.234568E+09. Also 1234.57E+05 with #####.00E+00 formatting. 
(In Excel ####0.00E+00 produces 1.23E+08, so we shouldn't use this interpretation since only 1 digit is 
produced before the decimal, the hash marks are optional and redundant when used with exponents)

So I propose that any scientific formatted number with the the 'grouping' flag set be interpreted as 
'Engineering' format when displayed. And that any scientific formatted cell set this flag when '#." occurs 
in the mantissa. This implies only a change in the cell format analyzer function and the formatting to a 
display string function. The number of min-integer-digits less the decimal-places would be the 
modulus increment for the exponent. 
Comment 115 irneb 2010-11-30 08:48:31 UTC
Good idea! In other words, when having a scientific format the grouping
indicates that it needs to "group" the exponent on amount of integers - i.e. the
max-integer-digits becomes the exponent increment factor.

And this should only happen if the digit preceding the decimal point in the
format string is a hash. That way the grouping works normally when the format is
0000.00E+0 ... so in that case 123456 shows as 1,234.56E+2. But if the format
was ####.00E+0 then it should display as 12.35E+4.

But to get that saved and then re applied after you open the file, there would
be no difference in the style saved in the file between the above samples. Or
should we just disallow grouping with any form of scientific notation? I.e. the
1st sample should read: 1234.56E+2 and the thousands grouping does not happen ever.
Comment 116 irneb 2010-11-30 10:54:17 UTC
Actually I've just tested. Whether you do a "User Defined" or "Scientific"
format, the "Thousands separator" check-box is greyed out anyway. Only thing is
that the comma separator should then not have an effect in the custom format string.
Comment 117 paulwolstenholme 2010-11-30 21:11:05 UTC
Great to see some progress at last...

1) File storage of format specification:

When considering how the format is encoded in spreadsheet files, please bear in
mind that OOo also supports some excel formats.  It would be great to see values
an excel user formatted as 'engineering' shown as engineering.

At present if OOo reads an excel97/200/xp file that has values that were
formatted as "##0.0E+0" in excel, they are shown in 000.0E+0 format.

2) Excel 'engineering' format vs. true engineering format

Excel's 'engineering' format has a variable number of significant digits - not a
fixed number of significant digits as traditionally used as engineering format.
 Excel got it wrong.  A little bit less than 1.00E+0 should be 990.E-3, not
990.00E-3 which pretends to be more accurate than it should.

Offering true engineering format would be a real bonus for OOo although I expect
many will say compatibility with excel is also important.
For true engineering format we would need to specify the number of mantissa
digits and accept that the decimal point will be inserted after the first,
second or third digit according to the value being displayed. How to express
that intention in a custom format would be an interesting question ( perhaps
0?00E+0 0?000E+0 0?0000E+0 )

I have no wish to see progress on this issue halted.  I believe it is more
important to provide any form of engineering notation than it is to fine-tune
that format.  If only one engineering format is possible, my vote would be for
what I describe here as true engineering format.

Keep up the good work!
Comment 118 nick_ee 2010-11-30 23:18:42 UTC

1) Your post got me to double check the behavior of Excel (Mac 2008): ###.00e+00 or ##0.00E+00 or 
#00.00E+00 all give a multiple of 3 exponent. But with varying leading zeros in the formatting. A 
leading 0 is put into the display number if needed: i.e. #00.00E+00 gives 01.23E+04 - Ughh. Very 
mechanical. But the main point is that any leading # gives rise to exponents as fixed multiples of some 
base number (again by 2, 3, 4, etc). My second point is that fixing the corner conditions of the display 
specification is fraught with interpretation issues. At some point in the counting there will be a 
nonlinearity in display step, and we need to accept it. 

2) I learned engineering notation as going from 1..1999 before rolling back to 3 significant digits as 
2.00e3 (think voltmeter displays) because the small increment going from 999 to 1000 swamps the 
mechanical shift to 1.00E3, as you say.

3) What everyone most wants is to be able to pin the exponent to multiples of 3 (and potentially other 
numbers) for faster visual comprehension and comparison, and we are discussing how best to do it. A 
full blown exponential format would be nice, but may be unnecessary if we can accept as described 
earlier to use the difference in mantissa integer digits and total mantissa digits along with the 'group' 
flag to interpret that as the exponent's multiple.

I would wait until we are adding support for SI units which will need a new storage format, to then also 
support a more explicit engineering display format with an added flag for number/unit selection.

An open question: I've been looking through the source code, especially in the zforscan.hxx .cxx and 
zformat.cxx files trying to interpret them. I'm limited because I don't have a build environment to 
recompile OO, and am only guessing where the call to create the output string or the call to analyze the 
format string start.
Comment 119 irneb 2010-12-01 05:43:48 UTC
I'd go further ... I'd say the use of the grouping switch can be seen as a
temporary, quick-fix solution for engineering format only. And yes, to get the
exponents incrementing by a user specified factor is the major requirement here.
For tweaking (which can happen at a later stage) around the increment points,
well need a bit more discussion. With some complex conditional formatting it can
even be possible to show SI prefixes if needed - Not a solution for SI, but
better than nothing.

But! I think there should be a more general purpose number format in the ODF
spec. Why they ignore the format string is beyond me. This seems like such an
inefficient way of doing it:

_When creating a custom format_
1. The user types the format string.
2. The format string is parsed to "interpret" it into the XML properties.
3. This is saved as the interpreted values, without "remembering" what the user

_When opening a file_
1. The XML's properties are read
2. When opening the Number Format dialog, the format string is regenerated from
some hard-coded rules

Would it not have been a lot more efficient, not to mention more comprehensive,
if the format string was saved instead (or even as well)?

I think before the SI thing is added, the ODF bureaucracy should take note that
there's holes in this section - I think a revamp of the number formatting may be
a much better solution in the long-run. Something which would not make it near
impossible to have other numbering systems as well. "Shouldn't" there be a
question of "Allow for un-thought-of scenarios" instead of "Cater for each
scenario we can think of"?

Something to the tune of saving the formatting in this general purpose way as
well as the current. But when the program has yet to implement it - a default
formatting is used. This way if there's any further formatting mods, older
versions of programs (not just could still work with the file (albeit
with a default format), the newer version could read the property(ies) which
explain how to correctly format the number and thus have this implemented there.
Comment 120 lml 2010-12-01 12:04:10 UTC

If we are talking about a robust, new solution, I suggest two things:
For nick_ee second thought (engineering notation as going from 1..1999 before
rolling back to 3 significant digits) I suggest to have a way to fix the
exponent for a specific value. Imagine a table containing current values in
milliampers. It could be good, if all the values in the table has az "E-03"
exponent (or "mA", but I don't want to talk about the SI notation now), even if
there are some values less than 1mA or some values are not less than 1000 (or
2000) mA. It makes the numbers more comparable for the human eyes.
Maybe there is a better solution instead of "format string". Why we need to
think about and learn a complicated, "coded" way to express the number of
digits, thousands separator, and colored negatives, etc? Maybe it was a good and
compact solution 20 years ago.
Maybe a better UI can exist for setting the parameters of the number
(representing the sign, the left and right part of the decimal separator and the
exponent). Most likely this UI is not yet invented, and our job it to do it. I'd
like to query why it is thought an expression like
[>=10000000]"RS "##\,##\,##\,##0;[>=100000]"RS " ##\,##\,##0;"RS "##,##0
is more user friendly than an UI containing some radio buttons and some fields
defining the number of digits. And we can use more than one but only very basic
format string instead of a big, complicated one.
Maybe I've went too far, and I'm wrong but I feel there are too many functions
stuffed into the format string, and it become too complicated (for an average
user). And we are talking about to put a new function into it, make it more
Comment 121 cno 2010-12-01 12:12:09 UTC
Don't forget to update the page (already mentioned before) with
solid conclusions/choices.
(I just came accross that page and obviously remembered this issue)
Comment 122 ooo 2010-12-01 12:44:53 UTC
*** Issue 115796 has been marked as a duplicate of this issue. ***
Comment 123 irneb 2010-12-01 13:23:45 UTC
Thanks yes, I'm in the process of editing it. Feel free to add / modify if you
have something to contribute to that page as well.
Comment 124 nick_ee 2010-12-02 00:20:20 UTC
Another way to do the formatting selection is to add an 'Engineering Exponent' check box to the 
Scientific Format screen. An example could be given at that point with a selectable increment number 
for the exponent corresponding to a maximum number of integer digits before the decimal point in the 
mantissa. Operationally this would adjust the number of #'s before the decimal without the user having 
to adjust things. The Excel "Custom:Type" or OO "Category: Format" windows show sample number 
formats according to the user's selection. Toggling the 'Engineering Exponent' check box would bring 
up the engineering templates:

This is a painless extension to the existing format example table. The 'leading zeros' could be replaced 
with 'exponent multiple' ranging from 1..something useful.

Also, we don't need a new 'F' suffix, it is non-standard. For generic "Scientific Format", all numbers look 
similar from the user's perspective. The format specification string level is where they differ, but the 
user just sees a number with hidden normalization and truncation rules. Show the 'E' numbers with the 
desired exponent presentation. Everybody knows how to interpret 32.12E+03. Most would be confused 
by 32.12F+03.
Comment 125 nick_ee 2010-12-03 12:40:48 UTC

I think we stop at this point and say that forcing leading zeros becomes a textual formatting issue. If 
you are using engineering format, you want the display to be a multiple of 3 (or something else), and 
that has to override the existing formatting.

Suggested decision tree: 

1) Scientific format Yes/No
2) Engineering exponent:  Yes - what multiple / No - stay with scientific exponent
3) leading digits: Yes/No
	- how many digits - maximum?
	- how many digits - must show? 
		(this could be 5 digits max, with 3 that must be shown)
4) decimal digits
	-how many max
	- how many minimum 
	-show trailing zeros?

A lot of these details are buried in the formatting string syntax. If we make each of these items explicit 
on the formatting screen, then the user gets a clearer picture of what they are configuring.

On Dec 2, 2010, at 11:48 PM, Irné Barnard wrote:

BTW, Excel even does the following:
000.00E+00 ... where I'd want at least 3 digits (zero padded) but still have true engineering notation. 
I.e. 1000 shows as 001.00E+03.

But how to get rid if that if what you actually want is for it to show 100.00E+01, as OOo.Calc would do 
at present?
Irné B
Comment 126 efa 2010-12-13 14:09:15 UTC
@ eadthem Sun Oct 10 13:05:38 +0000 2010

I agree that the first priority (and simpler task too), is to be able to display
a number with engineering notation:
First Exx with xx=3*n, with n integer positive or negative,
then secondly display a number with SI single letter suffix, in a separate
Issue# of course.

For SI formatting, it is important to note that SI, FORBID attaching (with no
space between) suffix+measure unit to the number.
Example: 10nF is explicitly forbidden by SI. The only correct format is: 10 nF.
Also forbidden are complete name of measure unit. So we cannot show and recognize:
1 metre, 1 meter, 1 metro, etc ..., only 1 m is valid and international too
(this is "International" System for measure unit)
Comment 127 irneb 2010-12-13 14:57:15 UTC
Now that I'd definitely put into a separate issue. I might even say, even though
it is "expressly" forbidden to have the SI prefix directly following the number
it is also the "de-facto-norm" to have it without the space. E.g. in my industry
we use ###mm and ##.###m constantly. It's usually frowned upon when you write
them ### mm ... so for that I might suggest an option for strict SI compliance
(maybe as default) which would simply insert a space directly following the number.

But as discussed, this whole SI conundrum needs a whole new formatting code in
itself - apart from a new page on the format dialog and coding to accomplish
such. The Eng formatting is just about fixable using the existing ODF spec ...
well if we "trick" it a bit. The SI would be too complex to try and "trick" the
ODF spec into working with it. So in the "short term" (what a laugh - 7.5 years
already) I wouldn't hold my breath.

Which actually makes me see this whole problem for what it is: It was reported
originally long before the ODF spec was even thought of. Yet it was totally
ignored when defining the spec. I think it was a scenario of trying to create
too inflexible / restrictive rules, and then running into one of the incredibly
prone situations of: "No-one can think of everything".
Comment 128 nick_ee 2010-12-14 07:09:41 UTC
Fixed exponent multiples have already been worked out elsewhere: specifically in the Java specification 
for the DecimalFormat Class
And they use the Microsoft specified format scientific formatting code where the number of #'s implies 
the exponent multiple. We reverse engineered stuff that already exists. I suspect it is consistent with 
how Microsoft does it's Excel formatting.

What this means is that a small chunk of Java code implementing a DecimalFormat object will do the 
formatting work for us. Useful if we can mix or translate Java to the C++ OO source format.

I would recommend superseding much of what we have discussed with this Java specification, or 

Comment 129 irneb 2010-12-14 08:55:57 UTC
That's all good and fine. I can see that there's a lot already done about these
formats in other places as well. E.g. my $10 Casio Scientific calculator has a
button to format numbers into Eng ... so the actual existing formatting
algorithms are definitely numerous and even quite efficient.

The "real" problem is how to save the format into the ODS file, so that next
time it opens the same format is applied again. That's where we don't have an
easy answer. Since the ODS file needs to be ODF compliant, we can't simply add
whatever we require to it. It needs to go through a change in the Open Document
Format specification.

As I've stated in a previous post, the ODF does not allow anywhere to save the
###.00E+00 format string itself (like Excel's XLSX file type does). It converts
the string back-and-forth into settings saved into the ODS file. These settings
are the following (and _ONLY_ the following):
- Number of decimal places (the .00 portion of the string translates to 2 in
this setting)
- 1000 grouping on / off
- Minimum number of exponent digits (the E+00 translates to 2)
- Minimum number of integer digits (the ### translates to 3 and back into 000)

Therefore if the user formats as ###.00E+00, saves closes and reopens the file
the format changes to 000.00E+00. Which then makes for values such as 1000
displayed as 100.00E+01 ... this is clearly incorrect.

There was a suggestion to "trick" the formatting by using the 1000's grouping to
denote that this is engineering format instead of scientific. Which would allow
only a modification of the Calc program - instead of a mod to the ODF spec. But
then we loose the ability to have 1000's grouping in "normal" scientific format.
E.g. 0,000.00E+00 would get translated to ####.00E+00. Given this is probably an
extreme example, it needs to be noted that we're loosing a possible format in
favour of another.

I'm all for the idea of allowing the grouping trick so the Eng format can happen
right now. It just needs to be designed so that when and if the ODF specs
finally get woken up to the "real" world ... we've not made ourselves a bigger
hole than we find ourselves in now. E.g. what would then happen when the newer,
correctly fixed version of Calc, opens an ODS file saved by this proposed
intermediate version?
Comment 130 paulwolstenholme 2010-12-14 21:09:51 UTC
I disagree with irneb who said:
"E.g. 0,000.00E+00 would get translated to ####.00E+00."
Excel actually interprets ####.00E+00 as meaning that exponents must be a
multiple of 4.

Excel custom codes do not seem to support 1000's grouping in scientific format
without also forcing the exponent to be a multiple of 4 or more, such as excel
custom codes 0,000.00E+00 or #,###.00E+00.  My tests on excel 2002 indicate that
excel counts the total number of integer digits in the custom code (the number
of '#' or '0' characters before the '.') and makes the exponent a multiple of
that number.

I believe there is no existing excel custom code that can simultaneously specify
scientific format, 1000's grouping and 'Minimum number of integer digits' = 1, 2
or 3.  This combination of attributes is actually not required because there is
no value that would ever use 1000's grouping.  We can safely use this
combination of attributes to represent engineering format.  I'm not sure why we
would want engineering format with leading 0's but the codes do support it - and
so does excel.

So I'm suggesting we interpret a combination of ODF file attributes of:
* 1000's grouping, 
* scientific format, and 
* 'Minimum number of integer digits' <= 3 
as denoting engineering notation.
Comment 131 irneb 2010-12-15 05:40:29 UTC
I'm not talking about Excel. I was referring to what would happen in Calc if we
do go with the grouping trick. I also did state that the lost form of formatting
is an extremely exceptional case, very few people (if any) would want such a
format - so we might not need worry on that score.

I was just stating such for completeness - trying to look at the problem and
solution from all angles. What I don't want is to go through this process only
to find a year later that we've got bigger problems due to what we've done now.
I.e. I'm trying to avoid the same mistakes made by the ODF council when they
designed this (IMO) silly portion of the spec.

I can guess why they went the route of settings instead of a format string -
it's generally more efficient. But using settings is not as open-ended as the
format string. This is one of those cases where efficiency was taken just that
bit too far, it breaks into functionality.
Comment 132 paulwolstenholme 2010-12-15 10:37:32 UTC
I haven't yet seen any way of specifying 1000's grouping in scientific format in
OOo with 'Minimum number of integer digits' <= 3.  I should have pointed out
that, as far as I can determine, OOo and excel are the same in this respect.

If this combination of attributes is indeed impossible to set at present, there
can be no OOo users making use of it.  Reassigning it to mean engineering format
is a promising option.

We could maintain the existing support for any users that are using 1000's
grouping in scientific format with 'Minimum number of integer digits' > 3 (which
is an OOo feature or trait that does not exist in excel).  To do this we would
turn off the engineering interpretation during 'Minimum number of integer
digits' > 3.

Finally, to look at the problem and solution from all angles, it is worthwhile
considering how existing software handles spreadsheet files that a future
version of OOo has coded as engineering format.  I would hope some sort of value
is shown and that the format can be changed - all without crashing or hanging or
deeming the file to be corrupt.
Comment 133 irneb 2010-12-15 15:18:33 UTC
Actually you're absolutely correct. That special case is more like an unintended

So we might use the grouping even for the SI idea: i.e. a format code of
,0.00E+00 (although a lot "strange") could mean use SI with 2 decimals. So the
xml settings would become: integers=1, decimals=2, exponents=1, grouping=yes.

This can clearly be differentiated from the proposed Eng format trick of using
,000.00E+00 : integers=3, decimals=2, exponents=2, grouping=yes. Since the
number of integers govern the exponent multiples ... which is what exactly when
it's one one?

And we can even accommodate other exponent multiples, e.g. ,00.00E+00 would show
1234 as 12.34E+02. The settings would be: integers=2, decimals=2, exponents=2,

And it "shouldn't" cause problems with normal scientific format, since we can
still have that working as is. In any case when using the above format strings
with the comma (denoting 1000 grouping) the comma is interpreted as a string to
add to the number. I.e. as is a format of ,000.00E+00 on a number of 1234
currently displays as ,123.40E+01 ... which is clearly impossible for the user
to have wanted.

Where there may be an issue is if (as stated) some extremely strange guy wants
to format like this: 0,000.00E+00 where 123456 would become 1,234.56E+02. If we
do go with this wangling idea, the only possible format option this fictitious
guy would have is 0000.00E+00, giving him 1234.56E+02. Because as soon as he
includes a comma (anywhere) into the format string he'll get: 12.35E+04

And yes it would also be a good idea to manually edit the style.xml file inside
an ODS (to match these proposed fixes) and see what happens when opening it in
current and older Calcs. You never know if for some reason there's a hiccup.
Comment 134 paulwolstenholme 2010-12-15 19:53:47 UTC
Let's not confuse the idea of encoding engineering format in a calc file (using
1000's grouping) with the issue of expressing engineering format in OOo's
equivalent to what excel describes as a 'custom code' string.

It would be confusing to make the user enter a comma in the format string just
because we have a (temporary?) situation that encodes his intention in a similar
manner to 1000's grouping.

It is consistent with the existing symbol definitions to express standard
engineering format as ##0.00E+00 since the # indicates the possible presence of
a digit with leading 0 supression.

Do we need to support leading zeros with engineering format?  I do not think so.
 Excel allows #00.00E+00 and 000.00E+00 to express engineering notation (i.e.
the exponent a multiple of 3) with a minimum of 2 or 3 digits before the decimal
point (i.e. leading zeros) and it does implement it.  Not wishing to be sexist,
I might suggest that a user of this format could be an extremely strange girl or
guy ;)  Perhaps a reasonable compromise here is to accept the code #00.00E+00
and show 1234 as 001.23E+03 (because I can't imagine a need to supress the first
leading zero but not the second).
Comment 135 efa 2010-12-22 12:35:09 UTC
added an RFE for engineering notation to OASIS Open Document Format.
As long times will be necessary, maybe for v1.3 we have a common format that
every spreasheet should support.
Comment 136 rickygold 2011-01-17 16:45:50 UTC
This is a very important feature, I'm sincerely astonished that after so many
years and updates it's still not available!
Even cheaper pocket-calculators have this function!!!
Comment 137 pc101 2011-01-23 15:04:14 UTC
This has been an open issue since 2002. May I be bold (foolish?) enough to make
a suggestion as to a resolution?

It would seem sensible for any change to keep/add compatibility with other
spreadsheet programs, such as excel and gnumneric in the areas of:
 * Saving and reading the data from a file
 * Having the option to display the data as it appears in other programs

The simplest way to achieve this point would seem to be the option to have calc
treat the # formatting character the same as in excel and gnumeric.

This would not address the issues/concerns expressed of:

1) Those who would want the current excel/gnumeric engineering format (using #
characters) to display numbers in the range 0.999 to 999.999 as non exponent
numbers. So that the end result would be a set of numbers like:
100e3, 10e3, 1e3, 999, 100, 10, 1, 0, 0.1, 0.01 0.001, 0.999, 1e-3, 10e-3 etc

2) Those who would like the exponents displayed as SI prefixes, so the end
result would be a set of numbers like:
100k, 10k, 1k, 999, 100, 10, 1, 0, 0.1, 0.01 0.001, 0.999, 1m, 10m etc

3) Backwards compatibility with how the # formating character currently works in

This could to be achieved by having an option inside of calc that affected how a
cell formatted using # formating characters was displayed:

"Engineering format display"
 * Standard (default)     [excel/gnumeric]
 * Engineering numeric    [as 1) above, exponents as numeric numbers]
 * Engineering SI display [as 2) above, exponents as SI prefixes]
 * Pre calc 3.x display   [as 3) above, backwards compatibility]

Other options could be added if required.

I am envisaging that the implementation for the change of display formatting
could be based on the way code currently works when a cell is formatted as a
percentage, in which the displayed number changes and a % character is added
into the cell display, but for all other calculation purposes it is as if
nothing has changed.

Would this proposal address 95% percent of peoples' concerns expressed in this
Comment 138 pc101 2011-01-23 20:34:39 UTC
Correction to previous post. My apologies:

1) Those who would want the current excel/gnumeric engineering format (using #
characters) to display numbers in the range 0 to 999.999 as non exponent
numbers. So that the end result would be a set of numbers displayed like:
100e3, 10e3, 1e3, 999, 100, 10, 1, 0, 999.999e-3, 100e-3, 10e-3, 1e-3, 100e-6,
10e-6, 1e-6 etc

2) Those who would like the exponents displayed as SI prefixes, so the end
result would be a set of numbers like:
100k, 10k, 1k, 999, 100, 10, 1, 0, 999.999m, 100m, 10m, 1m, 100u, 10u, 1u etc

3) Backwards compatibility with how the # formating character currently works in

This could to be achieved by having an option inside of calc that affected how a
cell formatted using # formating characters was displayed:

"Engineering format display"
 * Standard (default)      [excel/gnumeric]
 * Engineering Alt display [as 1) above, exponents as numeric numbers]
 * Engineering SI display  [as 2) above, exponents as SI prefixes]
 * Pre calc 3.x display    [as 3) above, backwards compatibility]

I personally would be happy just if the "excel/gnumeric" option was the only one
Other options could be added if required. For instance making the range of
numbers in "1)" that are displayed without an exponent a user definable range.
Comment 139 ke6pij 2011-01-24 17:57:11 UTC
What PC101 describes as option 1 and displays what is shown in the table of CS's
post of March 30th is all that is required for most of us to be able to use and
recommend OpenOffice  as a legit business tool.
The need is not complicated, really. Calculators do it!
Comment 140 mrprogrammer 2011-01-24 19:27:56 UTC
I believe we can use Format, Styles and Formatting with Format, Conditional Formatting to obtain 
engineering notation in Calc. As an example for 5 significant digits, make three styles, Zero, One, and 
Two that use number formats 0.0000E+00, 00.000E+00, 000.00E+00, respectively. Apply style Zero to 
the cell, say B2. This is scientific notation. Using conditional formatting on the cell, set condition 1 
formula MOD(INT(LOG10(B2));3)=1 to apply style One, and then set condition 2 formula 
MOD(INT(LOG10(B2));3)=2 to apply style Two.

Note that the Excel "solution" provides variable precision. Using ##0.00E+00, numbers whose exponent 
is a multiple of three are displayed with three significant digits. But numbers with exponents of the form 
3N+1 and 3N+2 are shown with four and five significant digits. I would think that most engineers and 
scientists would not think that using variable precision to display numbers is a real solution. The 
technique using styles and conditional formatting provides the same number of significant digits for all 
Comment 141 nick_ee 2011-01-24 21:04:33 UTC
mrprogrammer - you missed the entire point of Engineering format. We want fixed exponents (typically 0, 
3, 6, etc.) corresponding to the usual x1000 increments in common unit naming (thousands-kilo, 
millions-mega, etc). So I could have 4 thousand, or 45 thousand, or 453 thousand - i.e. 4e3, 45e3, or 
453e3. Yes the precision moves, but we need it to be that way. If you want fixed precision stay with 
'scientific format 4.00e3, 4.50e4, 4.53e5' which already exists and operates well.
Comment 142 nick_ee 2011-01-24 21:14:33 UTC
I've backed off on comments recently. People are talking about the OO file specification. If that is what is 
required to get the OO updated then so be it. It is an issue somewhat beyond the simple question of the 
desirability for engineering notation. 

What is it going to take to update the OO interface spec to drive the software update?
Comment 143 kjgriffin 2011-01-24 21:32:52 UTC
I agree with mrprogrammer:  it is important that the engineering notation have a stable and specifiable 
number of signifiant figures;  453 e6 / 100 does not equal 4 e6, nor the other way around.  Expansion and 
truncation of the number of digits displayed is contrary to the whole notion of significant digits.

Yes, the traditional scientific notation does this, but there are times it would be helpful to have both 
engineering notation and significant figures.  As has been pointed out, my 28 year old calculator manages 
this format without difficulty. 
Comment 144 pc101 2011-01-24 22:04:36 UTC
Like it or not excel is the standard. It allows engineering format in a
particular way using the # formatting character. Gnumeric also behaves the same
as excel. Surely the best thing to do is to finally make calc also behave (or
have the option to) the same way by default as excel/gnumeric? So that by
default a spreadsheet using the # formating character will display the same way
in all three programs?

Anything else must surely be a calc display only option, that does not affect
the data written to the file? Hence the suggestion of calc display options.

The points like "it is important that the engineering notation have a stable and
specifiable number of signifiant figures" are reasonable and valid, but
seriously miss the point of first dragging calc to the point of compatibility
with excel/gnumeric. Implement these other ideas as a calc display option for
cells formating using the # character if you wish, but leave the default option
as that compatible with excel/gnumeric.

Yes you can always apply conditional formating and/or macros to display what you
want, but this is much more complicated that using the # formatting character in

Introducing an entirely new formatting option does not seem sensible as this
would not be compatible with other programs.

Does anyone know what Lotus 1-2-3 used to do with cells formatted using the #
Comment 145 mrprogrammer 2011-01-25 17:14:48 UTC
Note that my suggestion yesterday *does* ensure that the exponent is a multiple of three.  π is shown 
as 3.1416E+00, 10π as 31.416E+00, 100π as 314.16E+00, 1000π as 3.1416E+03, 10000π as 
31.416E+03, π/10 as 314.16E-03,  π/100 as 31.416E-03, etc.  I will admit that I don't see a good way 
to handle the case where the user wants engineering notation but with only significant digit.  (There are 
only two places to put the decimal point  — either 0.E+00 or .0E+00 — without adding additional digit 
placeholders.)  But presumably that case would be rare and Excel's "solution" doesn't handle it either.  I 
agree that my suggestion is more complicated than Excel's kludge, but believe it is more correct.  I 
would welcome direct support for engineering notation, hopefully with fixed precision, in a future 
release but wanted to offer this idea as a current workaround.  And if we also have a way to support 
formats like ###.0E+00, even if they produce variable precision, even better.  But I'd hope our goal 
would be to do it right, rather than simply be compatible.
Comment 146 pc101 2011-01-25 19:43:47 UTC
Picking up again on the point:

"it is important that the engineering notation have a stable and specifiable
number of signifiant figures"

Excel can display a minimum number of significant digits, with or without
trailing (unnecessary zeros) using the # format character in engineering notation.

To display numbers in engineering format, ie the exponent in multiple powers of
three, using a custom format, some examples are:

The example 1 below will be in engineering format with:
  two numbers after the decimal point
  can display up to at least 3 significant digits
  one number in the exponent

The example 2 below will be in engineering format with:
  two numbers after the decimal point
  can display up to at least 3 significant digits
  two numbers in the exponent

The example 3 below will be in engineering format with:
  three numbers after the decimal point
  can display up to at least 4 significant digits
  one number in the exponent

The example 4 below will be in engineering format with:
  three numbers after the decimal point
  can display up to at least 4 significant digits
  two numbers in the exponent

The example 5 below will be in engineering format with:
  At least one and up to three numbers after the decimal point,
  but excluding trailing zeros
  can display at up to at least 4 significant digits
  one number in the exponent

I also found a reference at:

Engineers like to display numbers with the exponent as a power of three.

This can be done with a custom format such as ##0.00E+0 or ##0.0E+0 but this
gives poor results with number less than 1,000.

This custom format works better: [<0.001]##0.00E+0;[<1000] #0.00;##0.00E+0

Alternatives on this theme are:

Gnumeric (v1.10.8) pretty much displays an Excel 2007 worksheet, formatted as
described above, the same as Excel. Calc (v3.2.1) does not.

I have created a simple test worksheet in Excel 2007
And captured pdfs of how this displays in Excel 2007, Gnumeric v1.10.8 and calc

Hopefully I will be able to attached these files to this ticket (I think they
appear right at the top?).
Comment 147 pc101 2011-01-25 19:46:54 UTC
Created attachment 75642 [details]
Excel 2007 Engineering Format Examples Using # Formatting
Comment 148 pc101 2011-01-25 19:49:58 UTC
Created attachment 75643 [details]
Excel output display of "excel_engineering_notation_examples.xlsx"
Comment 149 pc101 2011-01-25 19:51:14 UTC
Created attachment 75644 [details]
Gnumeric output display of "excel_engineering_notation_examples.xlsx"
Comment 150 pc101 2011-01-25 19:52:03 UTC
Created attachment 75645 [details]
Calc output display of "excel_engineering_notation_examples.xlsx"
Comment 151 irneb 2011-01-26 12:24:04 UTC
I'm not sure what all the new discussion is about. We all know all that already.
And yes mrprogrammer's suggestion can work for nearly any ENG format. See the
last ODS file attached.
Comment 152 irneb 2011-01-26 12:25:15 UTC
Created attachment 75657 [details]
Eng format using conditionals
Comment 153 irneb 2011-01-26 12:33:53 UTC
Of course saving that to XLS(x) or opening from Excel2007 looses the conditional
formatting (since it seems Excel's conditional formatting works a bit different
as well).

I'll add this solution to the Wiki page as well. Just for completeness, it's not
yet a full solution as a round trip between Calc and Excel removes this
formatting, but at least you get a "true" Eng format which is still a "true"
number instead of a piece of text. Thus you can use it directly in other formulas.
Comment 154 efa 2011-01-26 13:30:07 UTC
Created attachment 75658 [details]
Implementation with Conditional formatting
Comment 155 efa 2011-01-26 13:31:14 UTC
the work around from 'mrprogrammer' is the simplest and best proposed until now.
I attached a variation sheet on the basis of the one from 'irneb'.
This is all I want for now. Thanks
Comment 156 mrprogrammer 2011-01-26 21:00:41 UTC
The attachments today from irneb and efa were close what I had in mind, but I should have made my own 
attachment with an example.  I will show two ways to produce engineering notation, one using conditional 
formatting as described previously, and one using the STYLE() function.  Note that it is not necessary to 
take the absolute value of MOD(INT(LOG10(Bn));3) because MOD(…;3) is by definition always non-negative.  
Using the STYLE() function allows the three conditional formats to be used for other purposes at the 
expense of complicating the target cell's formula to add "+STYLE(…)".
Comment 157 mrprogrammer 2011-01-26 21:02:27 UTC
Created attachment 75670 [details]
Engineering notation with conditional formatting or STYLE() function
Comment 158 nick_ee 2011-01-26 21:43:18 UTC
Your links don't take me anywhere (Mac + Safari) where is this attachment that has been mentioned?
Comment 159 irneb 2011-01-27 06:37:44 UTC
Don't know, maybe it's a problem with Safari. The links work fine for me in
Firefox (both on Windows and Linux) - it basically downloads the ODS file and
then opens it in Open Office.
Comment 160 nick_ee 2011-01-27 14:11:37 UTC
Rebooted computer - attachment showed up on next try. I see the application of the STYLE attribute to the 
cells, and am impressed by the result. I've looked through the documentation and don't quite understand 
how it is being done. It looks like the user will have to first define the Engineering style (i.e. Eng4M2)  
before it can be used. That being the case, it would be useful to distribute OO with some useful default 
formatting styles included, or perhaps we make a set of styles available as a separate distribution. In 
addition Excel does not read OO files. So saving the OO spreadsheet in Excel format looses the function 
definition, and we are back to the second point of this effort - some form of compatibility with Excel.

Comment 161 mrprogrammer 2011-01-27 17:00:29 UTC
A colleague pointed out that we must also handle engineering notation for negative numbers.  Thus the 
scale calculation needs to be MOD(INT(LOG10(ABS(Bn)+(Bn=0)));3) instead of the formulas given 
previously.  I will create a new attachment with this change and additional examples for zero and negative 
Comment 162 mrprogrammer 2011-01-27 17:02:58 UTC
Created attachment 75687 [details]
Techinque to handle zero values and negative numbers
Comment 163 irneb 2011-01-28 05:35:52 UTC
There is an ODF Add-In for MS Word / Excel / PowerPoint. But it does much the
same as saving the ODS file in Calc as a XLS file. I.e. loosing conditional
formatting and the style formula doesn't work.

You're correct about the need for interoperability. I can't seem to find any way
short of performing the previously mentioned modifications to Calc itself. There
is already a work-around method to get it working inside the ODS file - so all
that is needed is to modify the programming to suit. In which case the XLS(x)
export / import filter also needs some modification to write / read Excel's
"Eng" format correctly. And then we'd need to test if this modification would
still allow the ODS file to be opened in an older version of Calc - if not,
we'll need to find another way.
Comment 164 pc101 2011-01-29 23:40:50 UTC
Lots of good ideas.

In terms of an implementation that is available today, and has a good, simple
ui, it might be worthwhile looking at what gnumeric currently does:

1) It has a single tick box for "eng" formatting in its UI (with a user
definable fixed number of decimal places) as an option on its "Scientific"
formating page

2) It appears to have engineering notation compatibility with excel (I have only
tried limited tests)

I have attached a screen shot of the UI page as

So if gnumeric can do this, I can't in principle see why calc cannot do this
today, at least as a large step towards the utopia that all would eventually
like to achieve.
Comment 165 pc101 2011-01-29 23:43:03 UTC
Created attachment 75720 [details]
gnumeric v1.10.8 ui implementation of engineering notation format
Comment 166 Charles T. Bell 2011-09-21 17:06:19 UTC
This particular "enhancement" does not need to be coded into the sheet, but can be a selection that allows the SI data to be displayed as Engineering Notation without changing the values of the data.
The point of engineering notation is so the user can view the results in Engineering Notation, not alter the underlying data.
Comment 167 Charles T. Bell 2011-09-21 17:18:38 UTC
Created attachment 76795 [details]
Class for Engineering Notation, includes .h and .cpp

These compressed files (2) are the .h and .cpp files for the exponents class to change an input number into Engineering Notation Alpha-Numeric.  It does either the letter or the exponent.
Comment 168 Laurent BP 2015-03-10 16:43:37 UTC
ODF 1.3 defined a new attribute "number:exponent-interval" to scientific number to handle engineering notation
Comment 169 orcmid 2015-03-10 20:13:22 UTC
(In reply to Laurent BP from comment #168)
> ODF 1.3 defined a new attribute "number:exponent-interval" to scientific
> number to handle engineering notation
> See:

Whenever ODF 1.3 finally appears or is advanced to the point to know how ODF 1.3 documents may be differentiated from ODF 1.2 documents, this will be handy.
At that time, it might be possible to use the Excel format notation and automatically derive the required ODF 1.3 attributes.

Note that this does not cure the desire for SI units which are best treated quite differently, since the SI scaling is applied before the presentation in scientific/engineering notation.
E.g., 68.946E+03 g
vs    68.846E+00 kg

So the user simply does this by adding the SI suffix and ensuring that the value at the indicated scale or else there has to be something more involved, akin to the ways that "%" introduces scaling before presentation of a value.
Comment 170 orcmid 2015-03-10 20:52:14 UTC
(In reply to orcmid from comment #169)
> Note that this does not cure the desire for SI units which are best treated
> quite differently, since the SI scaling is applied before the presentation
> in scientific/engineering notation.
> E.g., 68.946E+03 g
> vs    68.846E+00 kg
  vs    68.946E+00 kg
Comment 171 Marcus 2017-05-20 10:44:04 UTC
Reset the assignee to the default "".