Apache OpenOffice (AOO) Bugzilla – Issue 5930
Engineering Notation
Last modified: 2017-05-20 10:44:04 UTC
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).
Hi Falko, 1 4 u Frank
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) Note: Supported by Excel 2003
Hello Niklas, please give approval for this evaluated OO.o 2.0 flagged issue. 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.
*** Issue 24373 has been marked as a duplicate of this issue. ***
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μ (etc) Frank
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.
*** Issue 25974 has been marked as a duplicate of this issue. ***
*** Issue 32688 has been marked as a duplicate of this issue. ***
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
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.
Then it is a hidden feature and the online help concerning scientific notations should be updated with this information.
Information about using engineering notation should be added to the file helpcontent2\source\text\shared\01\05020301.xhp
Dear nn and cs,
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 exponent). 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 -
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
@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?
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. Eike
*** Issue 48026 has been marked as a duplicate of this issue. ***
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.
I'd like to add my voice to those who think this is important. It is dissapointing to see it targetted to "OOo Later"
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, Brusque
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.
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.
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.
*** Issue 70834 has been marked as a duplicate of this issue. ***
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) case when value>=1000.0 : value /= 1000.0 expof10 +=3 when value>=100.0 : precision -= 2 when value>=10.0 : precision -= 1 end "%.*fe%d" % [precision-1, value, expof10] end number_to_engineering(15000) => "15e3" to ruby formatting number by Kevin on Tue Aug 23 07:40:20 GMT 2005 http://www.bigbold.com/snippets/user/Kevin ---------- 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.
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?
Hello, 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. Regards
Hi Niklas, these RFEs are in your ownership.
Hi, 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 ?! Cheers JJ
Should we be opening an issue for Standardization "Engineering Notation" by ISO ;-)
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.
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 (etc.) 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.
Hi, Gnumeric supports it, I just found :) Enjoy:! 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!!! JJ
Created attachment 52154 [details] lets the user format numerical values...
Created attachment 52155 [details] lets the user format numerical values...
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 sheet)
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.
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. JJ
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 trends. I am hoping for this enhancement to come through sooner than later. http://www.openoffice.org/issues/show_bug.cgi?id=30215 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.
Love the idea of having an option of showing SI abbreviation rather than the exponent. 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.
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?
swingkyd, good suggestion. Take a look at http://opendocument.xml.org/ and see if the procedure is listed there. Probably won't show up for a few years, much to late for many.
This issue is important and listed on the quarterly review for Calc: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project Therefore adjusting target to 3.x.
Now this is making me happy. No need to write custom cell formats to display proper scientific data.
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.
@kohei: Hey Master, have you got time to implement this?
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 OpenOffice.org
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 OpenOffice.org
Please implement this feature. It will really help lots of people who study engineering and physics.
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
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 purposes.
What is needed to make this one valuable change go forward?
1. SUMMARY I too would like engineering notation. To answer larsnooden's question below I believe OpenOffice.org 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 OpenOffice.org. 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. A. ENGINEERING NOTATION 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 http://en.wikipedia.org/wiki/Engineering_notation ] as this may help in the implementation of engineering notation. B. SI PREFIXES 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 http://en.wikipedia.org/wiki/Si_prefix ]. 2. WAY AHEAD 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 OpenOffice.org P3 definition at http://www.openoffice.org/issues/showattachment.cgi?attach_id=31957] => 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 CLOSED, (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. 3. DETAILED RECOMMENDATIONS Recommend that: A. Status of Issue [24373, "Engineering and SI/MKSA notation support for Calc"] be changed from CLOSED to REOPENED, 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.
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 (http://en.wikipedia.org/wiki/SI_prefix). 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!
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: http://mattmccutchen.net/measurements/ http://extensions.services.openoffice.org/project/measurements Nick
Created attachment 64755 [details] Macro to convert to Eng / Si notation
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.
Created attachment 64763 [details] Macro to convert to Eng / Si notation (revision 2)
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.
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. famo
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)
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 done. 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.
Created attachment 64774 [details] Custom (Engineering) Notation in MS Excel 2003
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.
@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 ..
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.
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.
Adding engineering may be an ideal task for the student internship: http://wiki.services.openoffice.org/wiki/OpenOffice.org_Internship/ProjectProposals
Created attachment 69662 [details] Engineering Notation in Gnome Calculator
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: http://coding.derkeiler.com/Archive/Python/comp.lang.python/2007-03/msg01958.html
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! Tom
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!
# eng_alpha.py # 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. else: 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 else: return fmt % (s * f) + LExp[s * n] else: return fmt % (s * f) + HExp[n * s] # ---------end of the function --------- if __name__ == "__main__": # Some tests... Try them by running "python eng.py" print "some tests" print "----------" print "0:", eng () print print "1.:", eng (1.) print "1.23:", eng (1.23) print "123:", eng (123) print "1234.567:", eng (1234.567) print print "11:", eng (11) print print "* * * Less than One Positive * * * " print print "0.4:", eng (0.4) print "0.0004:", eng (0.0004) print "0.0000004:", eng (0.0000004) print print "0.004:", eng (0.004) print # 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 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 "* * * * * * Negatives! * * * * * * * * " 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 "* * * * * Less than One Negative* * * " print 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 print "-0.004:", eng (-0.004) print print "* * * * * * Just over One Thousand * * * * " print 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)
# eng.py # 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. else: 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 eng.py" print "some tests" print "----------" print "0:", eng () print print "1.:", eng (1.) print "1.23:", eng (1.23) print "123:", eng (123) print "1234.567:", eng (1234.567) print print "11.:", eng (11.) print print "0.4:", eng (0.4) print print "0.004:", eng (0.004) print "0.04:", eng (-0.04) print print "0.001234567E-7:", eng (0.001234567E-7) print print "6,000,000:", eng (6000000) print "6,000,000,000:", eng (6000000000) print "6,000,000,000,000:", eng (6000000000000) print print "* * * * < 1,000, > 1 * * * * * * * * * * " 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 "* * * * * * * * * * * * * * " 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 "* * * * * * * * * * * * * * " print print "-0.4:", eng (-0.4) print "-0.0004:", eng (-0.0004) print "-0.0000004:", eng (-0.0000004) print 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)
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.
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.
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)! $.02
At the top of the web page (http://www.openoffice.org/issues/show_bug.cgi?id=5930) 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: - http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/OfficeDev/Number_Formats - http://api.openoffice.org/docs/common/ref/com/sun/star/util/NumberFormats.html 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.
Created attachment 70413 [details] Spreadsheet with conversion functions - enable macros to see
To get this thing moving, I've started a wiki page under the UX (User Experience) community. http://wiki.services.openoffice.org/wiki/Engineering_and_SI_Number_Format_in_Calc Please register if you would like to contribute on the discussion / design of this.
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.
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. See * 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
Lars, 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!
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 implement. 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'.
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>
Created attachment 71591 [details] Python code runable file
Created attachment 71592 [details] Python code runable displaying Engineering Notation in numeric format
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.
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.
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. example .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.
Created attachment 72110 [details] Python runable file showing Alpha SI Engineering Notation
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 list. 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.
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?
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 irc.freenode.com as eadthem.
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.
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). regards,
cc me
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. Nick
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 consistently!!! 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!
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!
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.
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.
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!!!
Maybe the guys over at LibreOffice will be more receptive. www.documentfoundation.org
Or more specific at: http://bugs.freedesktop.org/show_bug.cgi?id=30716
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.
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.
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 spot. 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.
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.
This is how a format of ###.00E+00 is saved inside the styles.xml inside the ODS file: <number:number-style style:name="N107"> <number:scientific-number number:decimal-places="2" number:min-integer-digits="3" number:min-exponent-digits="2"/> </number:number-style> And here's the spec: http://docs.oasis-open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05-part1.html#a_16_27_5__number_scientific-number_ 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.
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.
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.
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.
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!
Paul 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.
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 typed. _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 OOo.org) 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.
Hi, If we are talking about a robust, new solution, I suggest two things: 1) 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. 2) 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 complicated.
Don't forget to update the page (already mentioned before) http://wiki.services.openoffice.org/wiki/Engineering_and_SI_Number_Format_in_Calc with solid conclusions/choices. (I just came accross that page and obviously remembered this issue)
*** Issue 115796 has been marked as a duplicate of this issue. ***
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.
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: #.00E+01 ##.00E+02 ###.00E+03 ####.00E+04 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.
Irnéb 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
@ 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)
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".
Fixed exponent multiples have already been worked out elsewhere: specifically in the Java specification for the DecimalFormat Class (see http://download.oracle.com/javase/1.4.2/docs/api/java/text/DecimalFormat.html) 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 equivalent. Nick
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?
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.
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.
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.
Actually you're absolutely correct. That special case is more like an unintended possibility. 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, grouping=yes. 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.
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).
added 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.
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!!!
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 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.
EXACTLY!! 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!
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 exponents.
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.
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?
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.
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 excel/gnumeric 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 # character?
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.
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 ##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. I have created a simple test worksheet in Excel 2007 excel_engineering_notation_examples.xlsx And captured pdfs of how this displays in Excel 2007, Gnumeric v1.10.8 and calc v3.2.1. excel_engineering_notation_examples.pdf gnumeric_v1.10.8_engineering_notation_output_of_excel_Import.pdf calc_v3.2.1_engineering_notation_output_of_excel_import.pdf Hopefully I will be able to attached these files to this ticket (I think they appear right at the top?).
Created attachment 75642 [details] Excel 2007 Engineering Format Examples Using # Formatting
Created attachment 75643 [details] Excel output display of "excel_engineering_notation_examples.xlsx"
Created attachment 75644 [details] Gnumeric output display of "excel_engineering_notation_examples.xlsx"
Created attachment 75645 [details] Calc output display of "excel_engineering_notation_examples.xlsx"
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.
Created attachment 75657 [details] Eng format using conditionals
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.
Created attachment 75658 [details] Implementation with Conditional formatting
WOW! 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
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(…)".
Created attachment 75670 [details] Engineering notation with conditional formatting or STYLE() function
Your links don't take me anywhere (Mac + Safari) where is this attachment that has been mentioned?
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.
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.
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 numbers.
Created attachment 75687 [details] Techinque to handle zero values and negative numbers
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.
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 "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 towards the utopia that all would eventually like to achieve.
Created attachment 75720 [details] gnumeric v1.10.8 ui implementation of engineering notation format
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.
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.
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
(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: https://issues.oasis-open.org/browse/OFFICE-1828 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.
(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
Reset the assignee to the default "issues@openoffice.apache.org".