Ask Your Question
2

Why calc fraction returns wrong answer.

asked 2018-02-04 02:01:35 +0200

Richlib gravatar image

updated 2018-02-06 17:04:42 +0200

When I put .75 in a1 and put =a1 in a2 and format a2 as fraction, I get the number 1 instead of 3/4. I tried all of the present formats(??/??) but none gives me 3/4. Tried this in excel and WPS office and it work correctly, but not in Libre Office Calc. Try it for yourself

Thanks: Rich.

Here is a screenshot of calc. C:\fakepath\Libre Office Calc fractions.png

Here is an image if screenshot above doesn't work for you. image description

Other fractions work fine, just some fractions returns 1 in the cell. Can't remember which ones malfunctions. I'll try and find out for you and try to make a list. May take some time to do.

Here are fractions in 1/32 in increments. I have Libre Office Ver 5.4.4.2 image description

Here if a file with the fraction format ??\/?? I hope I did this up load correctly.
C:\fakepath\LO Fraction1.ods

Here is anoter attachment I'll send you. I just formatted to fractions. C:\fakepath\LO Fraction2.ods

Here are two more files to look at. one ODS the other XLSX

C:\fakepath\Decimal to Fraction.xlsx

C:\fakepath\Decimal to Fraction.ods

If the files I"m uploading to you do work on your Libre Office calc and don't work on mine that probably means that there is something wrong with my installation. That"s why I sent you screenshots of the the files in question. Is that possible? Should I try and uninstall and reinstall Libre Office? Thanks: Rich

Here is Decimals to Fractions.ods. C:\fakepath\Decimal to Fraction.ods

edit retag flag offensive close merge delete

Comments

Hello @Richlib,

i could not reproduce this behaviour in my LibreOffice Calc version 5.4.2.2 Build ID: 1:5.4.2-0ubuntu0.17.10.

Cell A2 comes out correctly as 3/4, after i had set its Numberformat to "Fraction" with Format Code # ?/?.

Regards, lib

librebel gravatar imagelibrebel ( 2018-02-04 02:26:38 +0200 )edit

Version?
Did Calc accept your lazy ".75" as 0.75?
Exact format code strings tested?
For me fraction formats work fine for a long time now and also in V 6.0.0..

Lupp gravatar imageLupp ( 2018-02-04 02:27:27 +0200 )edit

@Richlib: Please upload a document showing the issue for you. To do so edit your question and use the tool with the icon looking like a paperclip.

Lupp gravatar imageLupp ( 2018-02-04 02:32:08 +0200 )edit

How do I edit this some more I want to add another png calc file to sow you. It wont let me edit.

Richlib gravatar imageRichlib ( 2018-02-04 14:44:06 +0200 )edit

Thats ok I was able to edit and add a png screenshot.

Richlib gravatar imageRichlib ( 2018-02-04 14:51:49 +0200 )edit

perhaps this behaviour is somehow related to the "/" character inside the Format Code string...

Please try in these cases the following Format Code string instead:

# ??\/??

( placing a "\" in front of the "/" )

librebel gravatar imagelibrebel ( 2018-02-04 15:02:48 +0200 )edit

Sigh. Is that so difficult to upload a document as @Lupp asked, instead of the screenshots?

Is that impossible to mention program version? Unbelievable.

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-04 15:26:34 +0200 )edit

Don't know what you mean Mike, please clarify. I'm not an expert here, just learning.:)

Richlib gravatar imageRichlib ( 2018-02-04 16:13:01 +0200 )edit

The document is what you created once and are working in now and then.
To have another shift tomorrow with that document you will want to save it out of the RAM to a persistent representation: the file that eventually resides on your HD or somewhere, and that will be reloaded if needed.
There are different "filters" applicable when saving and different "formats" of the files. Knowledge about this (on my behalf) may be relevant for finding advice. I need the spreadsheet file.

Lupp gravatar imageLupp ( 2018-02-04 17:05:27 +0200 )edit

I uploaded 2 small files I was working on, is that what you mean? I saved both one in documents folder and the other on the desktop on my computer.. They are temporary small files to experiment on. Thanks for the info. :) My Libre Office Version 5.4.4.2 Build ID: 1:5.4.4~rc2-0ubuntu0.17.10.1~lo1

Richlib gravatar imageRichlib ( 2018-02-04 18:28:08 +0200 )edit

4 Answers

Sort by » oldest newest most voted
2

answered 2018-02-04 18:41:49 +0200

floris v gravatar image

Opened the second file. Cell A is formatted as Number, no specs for number of decimals. When I set that to 2 decimals, B1 displays 3/4. When I reduce it to 1 decimal, A1 displays 0.8 and B1 displays 4/5. Apparently Calc displays the number rounded to the selected number of decimals, and if nothing is entered there, it assumes 0 decimals and rounds to the nearest whole number..

edit flag offensive delete link more

Comments

Wow. Good catch. And I suppose, it is a bug. Formatting of original cell should not affect dependent cell formatting.

Mike Kaganski gravatar imageMike Kaganski ( 2018-02-04 19:41:11 +0200 )edit

I tried that but still the same result. Maybe a newer version of Libre Office is needed for ubuntu Mate 17.10

Richlib gravatar imageRichlib ( 2018-02-04 20:50:49 +0200 )edit

The document is set to "Precision as shown"!
That's a very dangerous option that should be abandoned, imo.
In addition: When I downloaded the "C:\fakepath\LO Fraction1.ods" attachment, the cell B1 had the absurd 'Numbers' format code #"/"##.

Lupp gravatar imageLupp ( 2018-02-04 20:57:06 +0200 )edit

That number format can not work as intended. Calculating with precision as shown in the context of fractions was enhanced for version 6.0

erAck gravatar imageerAck ( 2018-02-05 12:51:06 +0200 )edit
0

answered 2018-02-04 16:40:06 +0200

librebel gravatar image

Hello @Richlib,

Please try the following steps:

  1. select your cell B1;
  2. select the menu Format : Clear Direct Formatting;
  3. then set the correct Number Format again...

HTH, lib

edit flag offensive delete link more

Comments

This works. The cell is formatted to cell / format / numbers / general / 12/25.
It should be cell / format / numbers / fraction/ whatever.

paul1149 gravatar imagepaul1149 ( 2018-02-04 17:04:52 +0200 )edit

Hello All: Tried what you said but still getting a one(1). Tried using format in menu bar above but fractions selection is not there. Used right click selected fractions but still getting a one(1) as a result.

Richlib gravatar imageRichlib ( 2018-02-04 18:06:04 +0200 )edit

I just went ahead and entered the correct fractions via text and right margin formatted the affected cells. Thank all of you for trying to help me. It's just a small thing that's happening. I'm not going to quit using Libre Office, it's a great app. I'm still open to suggestions though. Again, thanks to all of you.

Richlib gravatar imageRichlib ( 2018-03-09 18:09:47 +0200 )edit
0

answered 2018-02-04 21:07:20 +0200

Lupp gravatar image

updated 2018-02-04 21:10:22 +0200

A follow-up to my comment on the comments on the answer by @floris v . See also this reworked version of the C:\fakepath\LO Fraction1.ods file here.

Disable Precision as shown in Tools > Options > LibreOffice Calc > Calculate.

edit flag offensive delete link more

Comments

Disabled precision as shown made no difference still the same ole number 1

Richlib gravatar imageRichlib ( 2018-02-04 22:05:38 +0200 )edit

The file I got didn't show the issue for me. Is there another one? Did you try the reworked file I uploaded in return?
Another hunch: You may try to (cautiously) reset your user profile. A corruption of the user profile is known to cause strange errors sometimes. See this guide.

Lupp gravatar imageLupp ( 2018-02-04 22:10:54 +0200 )edit

I opened both the files recently attached by editing to the OQ ("two more files: .ods and .xlsx) and checked the fractions displayed in column E. They were correct in every case. (Meanwhile I completed the test with another version. 5.4.4 and 6.0.0 showed no differences.)
Which cell (name one or two) showed a wrong fraction (which one) for you?
Did you try the suggestion concerning your user profile?

Lupp gravatar imageLupp ( 2018-02-05 11:43:11 +0200 )edit

If the files I"m uploading to you do work on your Libre Office calc and don't work on mine that probably means that there is something wrong with my installation. That"s why I sent you screenshots of the the files in question. Is that possible? Should I try and uninstall and reinstall Libre Office?

Richlib gravatar imageRichlib ( 2018-02-05 16:18:58 +0200 )edit

Did you read the suggestion concerning the user profile contained in my first comment to this answer and repeated in the second one?
If the user profile is corrupted, this will not be cured by a new install. As the UP contains the user's settings, templates, and "macros", LibO does not change or replace it when a new version is installed. Please read the guide I linked in above thoroughly.

Lupp gravatar imageLupp ( 2018-02-05 16:50:30 +0200 )edit

My user profile was ok according to the steps followed abive.

Richlib gravatar imageRichlib ( 2018-02-06 17:43:17 +0200 )edit
0

answered 2018-02-06 18:13:47 +0200

updated 2018-02-06 18:17:27 +0200

Have you updated LO? Showed 3/4 in a1 & a2 for me! Dell E6420, LO 5.3.4.2. Format to 123 4/9..OR 123 2/4! On both cells. Seems you may want reinstall LO or at least update. If it doesn't then. File a bug report.

edit flag offensive delete link more

Comments

Guess I'll have to file a bug report. Upgraded to ver 6 yesterday on one of my laptops via software updater. Still the same result 😊

Richlib gravatar imageRichlib ( 2018-02-09 16:20:05 +0200 )edit

Well, I tried Linux Mint off of a live CD and loaded up the LO calc fraction file and got the correct answer of "3/4". There where only 2 format options in "format sheet" for Fractions. One was the return of "3/4" and the other was "3/ 4" with the 4 moved over to the right one space. Iv'e gotten 2 upgrades of Libre Office Version. 6 from Ubuntu but it didn't change anything in LO calc Ubuntu Mate fraction problem. Does this information help anyone trying to help me troubleshoot this problem

Richlib gravatar imageRichlib ( 2018-02-15 20:45:56 +0200 )edit

It looks like to me that LO Calc fraction format is rounding the affected numbers Up. Maybe I'm wrong though.

Richlib gravatar imageRichlib ( 2018-02-15 20:48:52 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-04 02:01:35 +0200

Seen: 395 times

Last updated: Feb 06 '18