This one was originally requested in OOo. See the URL for details.
Currently it seems the OpenOffice guys doesn't want to implement a support for engineering notation. Would you be willing to implement it?
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
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.
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.
Ah, so Excel already supports true engineering notations?
Yes, I believe this was added sometime around 1996.
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?
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.
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.
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.
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.
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.
Created attachment 42704 [details] gnumeric v1.10.8 ui implementation of engineering notation format
*** Bug 36597 has been marked as a duplicate of this bug. ***
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
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.
There is a fix for the OOo bug, may be we can use the idea?
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.
opss, I involuntarily removed the assignee "kohei.yoshida@gmail.com"
restored the assignee, sorry
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
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!
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
Hello! I've tested it with LibreOffice Calc v4.3.2.2 running on Windows 7. This is still unimplemented. Regards, Brusque
No progress in Linux x64 4.3.5.2. Still missing.
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
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
start to implement attribute
That's great Laurent. Thanks for assuming this improvement.
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.
Seems there was no commit notification for Laurent's change, which is http://cgit.freedesktop.org/libreoffice/core/commit/?id=fdf8e5aaee49304df7f2f04fcdd932c3dd427703
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.
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.
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.
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".
Created attachment 114854 [details] Simplified test file
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.
*** Bug 114107 has been marked as a duplicate of this bug. ***