Bug 56205

Summary:	FORMATTING fractions displayed misleading and calculated wrong
Product:	LibreOffice	Reporter:	Horst <homaka>
Component:	Spreadsheet	Assignee:	Noel Power <nopower>
Status:	RESOLVED FIXED	QA Contact:
Severity:	normal
Priority:	medium	CC:	rb.henschel
Version:	3.6.2.2 release
Hardware:	Other
OS:	All
See Also:	https://bugs.freedesktop.org/show_bug.cgi?id=38097
Whiteboard:	target:4.0.0 target:3.6.5
i915 platform:		i915 features:
Bug Depends on:
Bug Blocks:	44446
Attachments:	TFRACXLS.XLSX TFRACXLS.XLSX

Description Horst 2012-10-19 21:35:17 UTC

Created attachment 68817 [details]
TFRACXLS.XLSX

Formatting a cell in fraction format the displayed results are misleading and wrong. The predefined formats are "# ?/?" which displays PI correctly as "3 1/7" as closest fraction. Creating userdefined formats like "?/?" or "??/??" produces still correct results. 
If you create userdefined formats with a fixed denominator like "?/n" (n={2..9}) the result is displayed as "#?/n"-Format with missing blank. The resulting string tricks you in believing you get the right result but you don't (see rows 3 to 5 in table).
The problem is that the parser interprets formats "?/n" (n={2..9}) as "#?/n" with missing displayed blank.

Format  PI display  should be
 ?/?      22/7       22/7
 ??/??   311/99     311/99 
 ?/8      31/8       25/8 or (3 1/8)
 ?/7      31/7       22/7 or (3 1/7)
 ?/6      31/6       19/6 or (3 1/6)

Furthermore if you save the file as .ods, close calc, open saved file all fraction formats "?/n" are reset to "?/?".
The number format dialog shows none of the previously defined formats, they are in the list but reset to a default format.

We might have 2 bugs here:
1) the reset/not saving part can be related to bug 38097
2) the display flaw is new and was discussed in the same bug

I created a test file with Excel TFRACXLS.XLSX. It might have one flaw line 2 shows 22/7 which is closer to PI than 311/99??
File opened in CALC has all formats but wrong display. Saving file as .ods, reopen -> formats resetted to ?/?.
Gnumeric has the same right behavior as Excel.
Saving the file in Excel as .ods results in chaos in CALC but not in Gnumeric (another bug??).

I tested it with LO3.5.5 up to 3.6.2.2.

Comment 1 Horst 2012-10-19 21:37:24 UTC

Created attachment 68818 [details]
TFRACXLS.XLSX

Comment 2 Regina Henschel 2012-10-26 07:02:54 UTC

There are two issues:
1. Wrong display
2. Lost fixed denominator in format

I have written bug 56419 for the lost format. So this issue can cover the problem with the wrong display.

Issue 38097 and issue 31449 seem different to me, because they are about formatting via style and here direct formatting is used.

Comment 3 Noel Power 2012-11-12 18:21:09 UTC

I'll have a crack at making the format force the denominator

Comment 4 Regina Henschel 2012-11-12 18:54:11 UTC

(In reply to comment #3)
> I'll have a crack at making the format force the denominator

? This issue is about the missing space between integer part and fraction.

Comment 5 Noel Power 2012-11-13 09:21:50 UTC

(In reply to comment #4)
> (In reply to comment #3)
> > I'll have a crack at making the format force the denominator
> 
> ? This issue is about the missing space between integer part and fraction.

Format   should be
?/8      25/8 or (3 1/8)

since ?/? does ( and has always in both excel and calc ) display 22/7 it seems to me that modifying the current display '31/8' by adding some space in order to get to a result like '3 1/8' isn't the right thing to do. 
For me it seems we should aim to have the resultant display be '25/8' to be consistent ( and also to make importing from xlsx easier ) That's what I mean by 'making the format force the denominator' That makes sense I hope

Comment 6 Regina Henschel 2012-11-13 13:30:37 UTC

Yes it is clear now.
Format code "# ?/8" should result in "3 1/8" display.
Format code "?/8" should result in "25/8" display.

Comment 7 Noel Power 2012-11-13 14:03:47 UTC

(In reply to comment #6)
> Yes it is clear now.
> Format code "# ?/8" should result in "3 1/8" display.
> Format code "?/8" should result in "25/8" display.

(In reply to comment #6)
> Yes it is clear now.
> Format code "# ?/8" should result in "3 1/8" display.
> Format code "?/8" should result in "25/8" display.

great, I am not very familiar with all those format possibilities and I just talked to Eike and he agrees with the above too, I will take bug

Comment 8 Not Assigned 2012-11-14 12:52:06 UTC

Noel Power committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0cd1ae1f9b85261e6915471938c5f6c3e0ec9283

don't always show integer part for fraction formats with hard denom fdo#56205



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.

Comment 9 Not Assigned 2012-11-14 14:58:14 UTC

Noel Power committed a patch related to this issue.
It has been pushed to "libreoffice-3-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4e57d13eca14fb4b0dd2c5a27eba1647b207b0d2&g=libreoffice-3-6

don't always show integer part for fraction formats with hard denom fdo#56205


It will be available in LibreOffice 3.6.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.

Comment 10 Horst 2012-11-14 23:35:13 UTC

Works fine in Version 3.6.5.0+ (Build ID: 95637e2). And fixed another bug (not reported). Inputline shows now correctly the decimal representation of the fraction, before we had the entered string. Good job guys:))

Comment 11 Noel Power 2012-11-15 07:56:19 UTC

marking as fixed

Comment 12 Noel Power 2012-11-15 09:07:25 UTC

(In reply to comment #10)
> Inputline shows now correctly the decimal representation of
> the fraction, before we had the entered string. Good job guys:))

that sounded sortof familiar :-) took me a while to recall but actually I believe this is the same as what was reported in bug #55875 ( there was a separate fix for that )

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.