[Issue] Format cells with engineering notation

Discuss the spreadsheet application
Post Reply
Jan Callesen
Posts: 1
Joined: Wed Mar 25, 2009 1:38 pm

[Issue] Format cells with engineering notation

Post by Jan Callesen »

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
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Format cells...

Post by Hagar Delest »

You should subscribe and vote for it (up to 2 votes per issue): Issue 5930 - Engineering Notation.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: [Issue] Format cells with engineering notation

Post by 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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
RickyGold
Posts: 6
Joined: Mon Jan 17, 2011 6:05 pm

Re: [Issue] Format cells with engineering notation

Post by 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 !!!!!!!
OpenOffice 3.4.1 on Windows XP Pro
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Format cells with engineering notation

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
RickyGold
Posts: 6
Joined: Mon Jan 17, 2011 6:05 pm

Re: [Issue] Format cells with engineering notation

Post by RickyGold »

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
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Issue] Format cells with engineering notation

Post by 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.
Attachments
201101171929.ods
Engineering notation example
(10.07 KiB) Downloaded 721 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
jonhayes
Posts: 2
Joined: Thu Jan 27, 2011 5:07 am

Re: [Issue] Format cells with engineering notation

Post by 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

MOD(INT(LOG10(CELL_TO_FORMAT));3)=1
to this:

Code: Select all

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
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Issue] Format cells with engineering notation

Post by 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.
Attachments
EngineeringV2.ods
(17.62 KiB) Downloaded 693 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
jonhayes
Posts: 2
Joined: Thu Jan 27, 2011 5:07 am

Re: [Issue] Format cells with engineering notation

Post by jonhayes »

Excellent, thank you!
OpenOffice 3.3 on Windows
Engineer CEng
Posts: 1
Joined: Thu Apr 07, 2011 9:44 am

Re: [Issue] Format cells with engineering notation

Post by Engineer CEng »

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
edogd
Posts: 4
Joined: Mon May 23, 2011 8:16 pm

Re: [Issue] Format cells with engineering notation

Post by 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

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/ ... 85#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/wik ... t_template
OpenOffice 3.3 on FC13 / LibreOffice 3.3 on Windows XP and CentOS
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Issue] Format cells with engineering notation

Post by 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?
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Format cells with engineering notation

Post by 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]. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
edogd
Posts: 4
Joined: Mon May 23, 2011 8:16 pm

Re: [Issue] Format cells with engineering notation

Post by 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.
OpenOffice 3.3 on FC13 / LibreOffice 3.3 on Windows XP and CentOS
Post Reply