[Issue] Format cells with engineering notation

[Hagar Delest]

I have a small problem using format cells.

I have tried to format the cells to "engineering mode" which is nearly the same as scientific mode.
The difference is whenever I write 0,001 I want the outcome to be 1E-3 not 100E-5. So far i have tried to create a userdefined mode which is ##0E+0.

So my question is if you are able to force open office to use E-3, E-6 and so on, the indices corresponding to mili, micro, nano etc.

And if not, is it possible that someone could develop such a mode?

Best regards,
Jan Callesen

[Hagar Delest]

You should subscribe and vote for it (up to 2 votes per issue): Issue 5930 - Engineering Notation.

[r4zoli]

Be aware of using spreadsheets in engineering world, it can cause lot of trouble, when you reach very small or very big numbers the rounding error can be big.
Some spreadsheet mistake$ mostly from accounting.

[RickyGold]

As far as I've experienced, Engineering Notation is not implemented and not reliably obtainable by specifying any userdefined mode.
As I've tried, if you create the userdefined mode "##0E+0" you will obtain an engineering notation number ONLY IF the cell contain a 3-characters number (100, 300, 550, etc); with 2-characters or 1-character numbers you find out it's scientific notation again.

I really hope to be wrong, so if anyone know how to do, I would really appreciate any suggestion.
On the other hand, If I'm right I really hope that Open Office developers will fix this problem soon.
I don't want to go back to M$ Office !!!!!!!

[Villeroy]

I don't want to go back to M$ Office !!!!!!!

Why not? If this praticular formatting feature is that important you should definitively go back to MS Office.

[RickyGold]

I don't want to go back to M$ Office !!!!!!!

Why not? If this praticular formatting feature is that important you should definitively go back to MS Office.

Because this "particular formatting feature" is commonly present even in the cheapest pocket calculators.
Given your experience and relevant role to the Forum, although reluctantly, I will have to focus strongly on your suggestion.

Thank you for replying.

[MrProgrammer]

You can use Format, Styles and Formatting with Format, Conditional Formatting to obtain engineering notation in Calc. See example in attachment. I have made three styles, Zero, One, and Two that use number formats 0.0000E+00, 00.000E+00, 000.00E+00, respectively. I applied style Zero to cells B2 through B25. This is scientific notation. Using conditional formatting on these cells with B2 as the active cell, I 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. If a different cell is the active (selected) cell when you apply conditional formatting to your sheet, specify that cell in those formulas.

I have a Casio FX-115 calculator on my desk that does the basics plus log and anti-log, trig/hyperbolic functions, fractions, powers, combinations/permutations, binary/octal/hexadecimal, six levels of parentheses, factorials, etc. It offers fixed, floating, and scientific display options but not engineering notation. Neither the calculator that came with Windows XP (the last release that I used) nor the one that comes with Mac OS X provides engineering notation.

If you read the details in issue 5930, you'll see that the real problem is in the Open Document Format (ODF), not in Calc. ODF is a published standard which has no provision for a way to represent in the ODS file that the spreadsheet user wants the exponent to be a multiple of 3. So, before this feature can be added to Calc, the ODF standard must be changed. At least that's the way I interpret the comments in the issue from irneb in November and December of 2010.

Excel can support things like this because it doesn't have to follow any standards. For users, that is good in the short term but bad in the long term because recent versions of Excel aren't required to open files created by very old Excel versions. I have personally experienced this issue and also with very old Powerpoint files, which sometimes have not opened after a new version is installed.

Edit: There are a couple of problems with Excel's "solution" to providing Engineering notation. The first is that it's undocumented. I don't know about the latest version of Excel, but in Excel 2003 there was no "Engineering" in the Format, Cells dialog. One had to know that it was possible to specify a user-defined format like ##0.00E+00 and that this meant that the user wanted engineering notation. But the Excel help doesn't say that anywhere that I can find. And using an undocumented feature always raises a red flag for me because if it's undocumented the vendor can change the behavior at any time. However one could argue that this particular vendor also changes documented features without warning, so what's the difference? The other problem with this "solution" is that it provides variable precision. In the format shown, 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. (Try it and see.) I would think that most engineers and scientists would not think that using variable precision to display numbers is a real solution. The OOo technique using styles and conditional formatting provides the same number of significant digits for all exponents.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[jonhayes]

I was playing around with the solution from Mr. Programmer and I liked it alot. However, I found that adding the ABS function, the negative numbers worked also. Mr. Programmer, could you please verify this as a good solution. I'm not sure where this might cause a problem.

I went from this:

Code: Select all   Expand viewCollapse view

MOD(INT(LOG10(CELL_TO_FORMAT));3)=1

to this:

Code: Select all   Expand viewCollapse view

MOD(INT(LOG10(ABS(CELL_TO_FORMAT)));3)=1

Thanks for posting this, it is very helpful.

Best regards,
Jon Hayes

[MrProgrammer]

I'm glad that you found this idea helpful. Thank you for pointing out the case of negative numbers. I would suggest using MOD(INT(LOG10(ABS(Bn)+(Bn=0)));3) so that zero values are also handled correctly (Bn is the cell to be formatted). I'll attach an example showing positive, zero, and negative numbers with 2 to 5 significant digits. It also shows how to handle engineering notation with the STYLE() function instead of conditional formatting.

[jonhayes]

Excellent, thank you!

[Engineer CEng]

Great work Mr Programmer et alia. Thank you for a clever but necessarily cumbersome workaround for a basic deficiency in Calc.

[edogd]

Awesome solution.

I would like to add to the solution by having a macro that automatically adds the conditional formatting, once you have the styles in you document. You can then assign a hot key to make it easier to use.

Code: Select all   Expand viewCollapse view

Sub createEngNotationConditionalFormatting

   Dim oSelectionRange as Object, oSelectionConditions as Object
'   Xray ThisComponent
   oSelectionRange = ThisComponent.CurrentSelection                        ' Store Selection Range
   oSelectionConditions = oSelectionRange.getPropertyValue("ConditionalFormat")   ' Store Selection Conditions
   oSelectionConditions.clear()  'Clear out all other conditional formating
'   Xray oSelectionRange      
   
   Dim oNewCondition(2) as new com.sun.star.beans.PropertyValue               ' Create New Property Value
   
   ' Add 1st Condition
   oNewCondition(0).Name = "Operator"
   oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA  'A formula will set this condition
   oNewCondition(1).Name = "Formula1"
   oNewCondition(1).Value = "MOD(INT(LOG10(ABS(A1)+(A1=0))),3)=0" 'Custom Formula
   oNewCondition(2).Name = "StyleName"
   oNewCondition(2).Value = "Eng5M0"   'Change to your Style name
   oSelectionConditions.addNew(oNewCondition()) 'adds the conditional formatting

   ' Add 2nd Condition   
   oNewCondition(0).Name = "Operator"
   oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA  'A formula will set this condition
   oNewCondition(1).Name = "Formula1"
   oNewCondition(1).Value = "MOD(INT(LOG10(ABS(A1)+(A1=0))),3)=1" 'Custom Formula
   oNewCondition(2).Name = "StyleName"
   oNewCondition(2).Value = "Eng5M1"   'Change to your Style name
   oSelectionConditions.addNew(oNewCondition()) 'adds the conditional formatting
   
   ' Add 3rd Condition
   oNewCondition(0).Name = "Operator"
   oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA  'A formulat will set this condition
   oNewCondition(1).Name = "Formula1"
   oNewCondition(1).Value = "MOD(INT(LOG10(ABS(A1)+(A1=0))),3)=2" 'Custom Formula
   oNewCondition(2).Name = "StyleName"
   oNewCondition(2).Value = "Eng5M2"   'Change to your Style name
   oSelectionConditions.addNew(oNewCondition()) 'adds the conditional formatting
      
   ' Update conditional formating
   oSelectionRange.setPropertyValue("ConditionalFormat", oSelectionConditions)   
   oSelectionRange.setPropertyValue("ConditionalFormatLocal", oSelectionConditions)   
   

End Sub


Thanks to the post http://user.services.openoffice.org/en/forum/viewtopic.php?f=45&t=39051&p=189885#p189885 for the conditional formatting macro.

You can make the macro available for all your calc documents by placing it in the My Macros->Standard->Module1 module from the Tools->Macros->Organize Macros->OpenOffice.org Basic.

Add the styles by default for all future spreadsheets by using the procedure in:
http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Setting_a_default_template

[MrProgrammer]

I see that the createEngNotationConditionalFormatting macro contains the text expression MOD(INT(LOG10(ABS(A1)+(A1=0))),3).
Does that mean that this macro only works if used on a selection where the active cell is A1?

[Villeroy]

It uses a relative reference in a conditional format relative to anchor cell $A$1 which would be the active cell when you do the same in the GUI.

Edit: ... and it might fail when applied to a sheet other than sheet #1 (index 0) because the anchor of the relative reference is the first cell on the first sheet unless you adjust pseudo-property SourcePosition [c.s.s.sheet.CellAddress].

[edogd]

It is supposed to use relative referencing. And it seems to be with respect to the current sheet. I tested it on multiple sheets and works fine.
I did it like this so you don't have to copy your cell value to another cell to apply the conditional formatting, you can directly apply to the number at in the cell.