Bug 30716 - Engineering Notation
Summary: Engineering Notation
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high enhancement
Assignee: Laurent Balland
URL: https://issues.apache.org/ooo/show_bu...
Whiteboard: feature target:5.0.0
Keywords:
: 36597 114107 (view as bug list)
Depends on:
Blocks:
 
Reported: 2010-10-08 11:53 UTC by Kohei Yoshida
Modified: 2017-11-28 05:56 UTC (History)
12 users (show)

See Also:
Crash report or crash signature:


Attachments
gnumeric v1.10.8 ui implementation of engineering notation format (36.63 KB, image/png)
2011-01-29 15:47 UTC, junk_2010
Details
Compressed file containing .CPP and .H files implementing Engineering Notation! (835 bytes, application/gzip)
2011-09-30 21:02 UTC, Charles Bell
Details
Simplified test file (16.46 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-04-17 09:57 UTC, Laurent Balland
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kohei Yoshida 2010-10-08 11:53:51 UTC
This one was originally requested in OOo.  See the URL for details.
Comment 1 klinger.ofir 2010-11-28 23:01:50 UTC
Currently it seems the OpenOffice guys doesn't want to implement a support for engineering notation.

Would you be willing to implement it?
Comment 2 klinger.ofir 2010-11-28 23:07:01 UTC
Original bug description:
The spreadsheet application (Calc) don't support "engineering notation".

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

I encourage you to read through the original bug for reasons why this is important:
http://www.openoffice.org/issues/show_bug.cgi?id=5930
Comment 3 Nick 2010-11-29 04:20:57 UTC
Equivalent to OO bug 5930 :

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.

I propose an additional variable in the cell formatting data structure, an integer modulo number identified as described above, from the number of #'s immediately before the '.'.  Not sure exactly where the cell format structure is stored. Perhaps the zforscan.cxx and zformat.cxx files are required to be modified. Not sure exactly.
Comment 4 moseby 2011-01-05 07:37:44 UTC
When you read the OOo case, ignore all of the discussion regarding the display of SI unit text (eg. 12450 displayed as "12.45k").  I get 99% (990e-3) of what I need with just plain engineering notation where the exponent is constrained to a multiple of 3 (eg. 12.45e3).  

SI units are cute and would make a good feature for the product, but I don't want it to delay basic engineering notation.
Comment 5 Kohei Yoshida 2011-01-05 07:45:06 UTC
Ah, so Excel already supports true engineering notations?
Comment 6 Brandon Ehle 2011-01-20 14:17:52 UTC
Yes, I believe this was added sometime around 1996.
Comment 7 junk_2010 2011-01-23 08:03:03 UTC
This has been an open issue since 2002:
http://www.openoffice.org/issues/show_bug.cgi?id=5930
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 gnumeric 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 number 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 calc

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 issue?
Comment 8 junk_2010 2011-01-23 12:38:31 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
calc

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 implemented.
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 9 Kohei Yoshida 2011-01-24 06:37:22 UTC
Let's not inflate this enhancement requests with additional parts.  This is already becoming a "kitchen sink" enhancement request that will never get implemented (as in the OOo counterpart).

I say that we'll focus on the interoperability aspect of this functionality in this bug report, which will make it easier and probably faster to make it happen.
Comment 10 junk_2010 2011-01-24 13:49:52 UTC
I agree with the comment expressed by "Kohei Yoshida"
"Let's not inflate this enhancement requests with additional parts"

As I said in the previous post:


It would seem sensible for any change to keep/add compatibility with other
spreadsheet programs, such as excel and gnumeric 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.


The reason for suggestion additional options, is to try to make the change more acceptable to those who:
a) Would want to have the option to keep the calc # formating working as it always has (haven't meet anyone yet, but there are bound to be some)

b) Looking at the corresponding openoffice 5930 bug, others seem to object to just making the # formating compatible with excel/gnumeric and want something more complicated, and don't seem to be thinking about compatibility with other spreadsheet programs.

I was foolishly trying to suggest something that might give people 95% of what they want and keep compatibility with the likes of excel/gnumeric for those to who this is important.
Comment 11 junk_2010 2011-01-25 12:01:28 UTC
Some further information I have discovered about the way Excel uses the # character for formatting is below. In the corresponding 5930 Open Office ticket I have attached an example excel spreadsheet and pdfs of the display output from Excel, gnumeric and calc. This just makes me believe more and that if calc could just behave the same as excel and gnumeric we would have a 95%+ solution.

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 example 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
##0.00E+0

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
##0.00E+00

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
##0.000E+0

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
##0.000E+00

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
##0.0##E+0

I also found a reference at:
http://people.stfx.ca/bliengme/ExcelTips/EngineeringNotation.htm

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:
[<0.001]##0.00E+00;[<1000]#0.00;##0.00E+00
[<0.001]##0.0###E+00;[<1000]#0.0###;##0.0###E+00

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.
Comment 12 junk_2010 2011-01-29 15:45:37 UTC
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 "gnumeric_v1.10.8_ui_engineering_notation_format.png"



So if gnumeric can do this, I can't in principle see why calc cannot do this today, at least as a large step forward.
Comment 13 junk_2010 2011-01-29 15:47:18 UTC
Created attachment 42704 [details]
gnumeric v1.10.8 ui implementation of engineering notation format
Comment 14 Rainer Bielefeld Retired 2011-05-05 23:53:09 UTC
*** Bug 36597 has been marked as a duplicate of this bug. ***
Comment 15 Ulrich Lukas 2011-09-16 16:26:06 UTC
Keywords: engineering notation, scientific representation, significant digits


Hi,

This issue can be clarified very easily:

1.) We need correct handling and representation of the number of significant
    digits in Calc. E.g. the use of "0.25kg" instead of "250g",
    if this number is the result of a measurement with only two significant
    digits.   ==> See:  http://en.wikipedia.org/wiki/Significant_figures

2.) It is common for engineers to always use exactly three significant
    digits. This /automatically/ results in a numeric range of "0.00 to 999"
    with EITHER the power of ten (or order of magnitude) in three-increments
    OR the appropriate SI-unit-prefix attached as a single suffix.
    E.g. as in "10.0 MHz" equal to "10.0E+6 Hz".
    If no dislplaying of units is desired, as in Calc, this could be
    displayed as "10.0M" OR "10.0E+6". /Altering/ a smaller number of
    significant figures, for example by "simplifying" the representation of 
    "0.1E+9" equaling "0.1G" to "100E+6" or "100M" is NOT appropriate.


It is important to note that the error described in point 2.) is a common misconception among engineers. It is, although very common, NEVER correct.

Luckily, all that the engineer really wants in this context is the
"power-of-ten-in-three-increments" or SI prefix formatting, so there is NO, ABSOLUTELY NO reason to do it wrongly.


==> Please implement this.
==> Please don't wait until inferior, competitive office products do.
==> Suggested priority: P2

See also:
http://en.wikipedia.org/wiki/SI_prefixes
http://en.wikipedia.org/wiki/Significant_figures
Comment 16 Charles Bell 2011-09-30 21:02:51 UTC
Created attachment 51834 [details]
Compressed file containing .CPP and .H files implementing Engineering Notation!

I have included a compressed file containing two files, .cpp and .h, which do work and display a number in Engineering Notation format either Alpha or Numeric.
This is just an example of one way to implement Engineering Notation.
Comment 17 Rainer Bielefeld Retired 2011-10-31 11:27:30 UTC
There is a fix for the OOo bug, may be we can use the idea?
Comment 18 Valerio Messina 2012-02-20 05:34:37 UTC
in 2010 I open an RFE for engineering notation to OASIS Open Document Format.
http://tools.oasis-open.org/issues/browse/OFFICE-3578
As long times will be necessary, maybe for v1.3 we have a common format that
every spreasheet should support.
Comment 19 Valerio Messina 2012-02-20 05:51:22 UTC
opss, I involuntarily removed the assignee "kohei.yoshida@gmail.com"
Comment 20 Valerio Messina 2012-02-22 02:35:17 UTC
restored the assignee, sorry
Comment 21 Edson Brusque 2012-11-28 20:41:13 UTC
Hello!

I'm the original poster of this issue.

In 10 years it's unbelievable that it's still pending as every engineer that uses Calc would love to have this implemented.

What can I do to help?

Best regards,

Edson Brusque
Comment 22 joachim.schwender 2014-05-13 19:13:30 UTC
I completely sign this request. I currently have some calc functions doign that, but this is a ridiculous work around. Any better solution would be highly appreciated!
Comment 23 QA Administrators 2014-10-23 17:32:09 UTC
Please read this message in its entirety before responding.

Your bug was confirmed at least 1 year ago and has not had any activity on it for over a year. Your bug is still set to NEW which means that it is open and confirmed. It would be nice to have the bug confirmed on a newer version than the version reported in the original report to know that the bug is still present -- sometimes a bug is inadvertently fixed over time and just never closed.

If you have time please do the following:
1) Test to see if the bug is still present on a currently supported version of LibreOffice (preferably 4.2 or newer).
2) If it is present please leave a comment telling us what version of LibreOffice and your operating system.
3) If it is NOT present please set the bug to RESOLVED-WORKSFORME and leave a short comment telling us your version and Operating System

Please DO NOT
1) Update the version field
2) Reply via email (please reply directly on the bug tracker)
3) Set the bug to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:
https://wiki.documentfoundation.org/QA/BugTriage

There are also other ways to get involved including with marketing, UX, documentation, and of course developing -  http://www.libreoffice.org/get-help/mailing-lists/. 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Comment 24 Edson Brusque 2014-10-23 17:47:27 UTC
Hello!

    I've tested it with LibreOffice Calc v4.3.2.2 running on Windows 7.

    This is still unimplemented.

    Regards,

    Brusque
Comment 25 moseby 2015-01-23 18:37:10 UTC
No progress in Linux x64 4.3.5.2.  Still missing.
Comment 26 Laurent Balland 2015-03-09 21:36:35 UTC
ODF 1.3 defined a new attribute "number:exponent-interval" to scientific number to handle engineering notation
See: https://issues.oasis-open.org/browse/OFFICE-1828
Comment 27 Edson Brusque 2015-03-10 11:38:16 UTC
That's good news.

Now we're 13 years and counting... that's a lot of time to wait for a so important (and simple) feature.

Microsoft Excel handles this without problem and without any conflict with document formats.

Regards,

Edson Brusque
Comment 28 Laurent Balland 2015-03-14 12:20:39 UTC
start to implement attribute
Comment 29 Edson Brusque 2015-03-16 11:41:13 UTC
That's great Laurent. Thanks for assuming this improvement.
Comment 30 Commit Notification 2015-03-22 15:49:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bff1b48899c73d381e675eb647f85c01c6fd354f

read both, XML_NAMESPACE_LO_EXT and XML_NAMESPACE_NUMBER, tdf#30716 follow-up

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 31 Eike Rathke 2015-03-22 16:38:07 UTC
Seems there was no commit notification for Laurent's change, which is http://cgit.freedesktop.org/libreoffice/core/commit/?id=fdf8e5aaee49304df7f2f04fcdd932c3dd427703
Comment 32 Commit Notification 2015-03-22 16:55:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=ee7e182591cc2a1e43f7d44204f00a316f22f07b

write exponent-interval only for 1.2+ and later, tdf#30716 follow-up

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 33 Commit Notification 2015-03-23 09:24:14 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=86a318ce629fbddb442f8f8ca762eeb3a51c9c6e

tdf#30716 Engineering notation

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 34 Commit Notification 2015-03-23 09:27:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e19a11c788d9772c0a27d079819033b7b3e8937b

more engineering notation format tests, tdf#30716

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 35 Eike Rathke 2015-03-23 12:22:00 UTC
Just to mention, http://cgit.freedesktop.org/libreoffice/core/commit/?id=7b7f5c70c650c2c74e0f266b57630d033fcf27a3 now correctly implements the "if and in which namespace to save exponent-interval".
Comment 36 Laurent Balland 2015-04-17 09:57:45 UTC
Created attachment 114854 [details]
Simplified test file
Comment 37 OfficeUser 2015-04-19 09:59:05 UTC
Since we have Engineering Notation implemented now with this bug report, it would be nice to additional support to display SI units.

Gnumeric already supports that.

I have filed Bug 90708 therefore.
Comment 38 V Stuart Foote 2017-11-28 05:56:51 UTC
*** Bug 114107 has been marked as a duplicate of this bug. ***