[Issue] Format cells with engineering notation

Discuss the spreadsheet application

[Issue] Format cells with engineering notation

Postby Jan Callesen » Wed Mar 25, 2009 3:54 pm

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
Last edited by Hagar Delest on Wed Mar 25, 2009 4:13 pm, edited 1 time in total.
Reason: Tagged the thread as issue (link to a bug report).
OOo 3.0.X on Mac OSx Leopard
Jan Callesen
 
Posts: 1
Joined: Wed Mar 25, 2009 1:38 pm

Re: Format cells...

Postby Hagar Delest » Wed Mar 25, 2009 4:12 pm

You should subscribe and vote for it (up to 2 votes per issue): Issue 5930 - Engineering Notation.
LibreOffice 7.0.3 on Xubuntu 20.10 and LibreOffice 6.4.6 (portable) on Windows 10.
User avatar
Hagar Delest
Moderator
 
Posts: 29399
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: [Issue] Format cells with engineering notation

Postby r4zoli » Wed Mar 25, 2009 4:56 pm

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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: [Issue] Format cells with engineering notation

Postby RickyGold » Mon Jan 17, 2011 6:25 pm

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 !!!!!!!
OpenOffice 3.4.1 on Windows XP Pro
RickyGold
 
Posts: 6
Joined: Mon Jan 17, 2011 6:05 pm

Re: [Issue] Format cells with engineering notation

Postby Villeroy » Mon Jan 17, 2011 7:36 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Format cells with engineering notation

Postby RickyGold » Tue Jan 18, 2011 2:32 am

Villeroy wrote:
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.
OpenOffice 3.4.1 on Windows XP Pro
RickyGold
 
Posts: 6
Joined: Mon Jan 17, 2011 6:05 pm

Re: [Issue] Format cells with engineering notation

Postby MrProgrammer » Tue Jan 18, 2011 4:01 am

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.
Attachments
201101171929.ods
Engineering notation example
(10.07 KiB) Downloaded 608 times
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3900
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Issue] Format cells with engineering notation

Postby jonhayes » Thu Jan 27, 2011 5:17 am

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
OpenOffice 3.3 on Windows
jonhayes
 
Posts: 2
Joined: Thu Jan 27, 2011 5:07 am

Re: [Issue] Format cells with engineering notation

Postby MrProgrammer » Thu Jan 27, 2011 7:14 pm

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.
Attachments
EngineeringV2.ods
(17.62 KiB) Downloaded 581 times
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3900
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Issue] Format cells with engineering notation

Postby jonhayes » Fri Jan 28, 2011 2:45 am

Excellent, thank you!
OpenOffice 3.3 on Windows
jonhayes
 
Posts: 2
Joined: Thu Jan 27, 2011 5:07 am

Re: [Issue] Format cells with engineering notation

Postby Engineer CEng » Thu Apr 07, 2011 9:58 am

Great work Mr Programmer et alia. Thank you for a clever but necessarily cumbersome workaround for a basic deficiency in Calc.
Engineer
OpenOffice 3,3,0 Build 9567 on Winddows 7
Engineer CEng
 
Posts: 1
Joined: Thu Apr 07, 2011 9:44 am

Re: [Issue] Format cells with engineering notation

Postby edogd » Tue May 24, 2011 2:35 am

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
OpenOffice 3.3 on FC13 / LibreOffice 3.3 on Windows XP and CentOS
edogd
 
Posts: 4
Joined: Mon May 23, 2011 8:16 pm

Re: [Issue] Format cells with engineering notation

Postby MrProgrammer » Wed May 25, 2011 7:54 pm

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?
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3900
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Issue] Format cells with engineering notation

Postby Villeroy » Wed May 25, 2011 8:08 pm

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]. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29259
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Format cells with engineering notation

Postby edogd » Wed May 25, 2011 8:29 pm

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.
OpenOffice 3.3 on FC13 / LibreOffice 3.3 on Windows XP and CentOS
edogd
 
Posts: 4
Joined: Mon May 23, 2011 8:16 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests