Fraction Formating is missleading - and different from Excel

English
#1

Hi. I found something curious. (There has been already a discussion about this finding here in 2018 with no feasible result)

If you choose Fraction Formating easly been shown missleading numbers.
“=30/360” - 30 days of a year (360) will be shown as “1/9” instead of “1/12”

This goes to Fraction Formating as default as “123 5/6”. If you choose formatting as “123 31/41” it will be shown correct as 1/12.

This seems a problem as well as Numbers (Apple) as well with Excel.

BUT: Excel shows a “0”, Numbers as well “1/9”

I don’t know how to fix this. Correct would be i.e. an industry standard of default formatting 5 leading digits, means “123 12345/99999”

In older versions the formating chooses correct “auto-expansion” of the fraction.

The Excel behavior is more save, but also incorrect. In reports or manuals for i.e. chemical processing this could lead to really bad things.

We could sum up, that 1/9 is not 1/12. This is wrong and unexpected to humans.

Please contribute your opinion.

#2

How you formatted the result cell?
Please upload your ODF type sample file here.
.
Here is a sample with formatting predefined custom cell style:
twelfth.ods (9.7 KB)

#3

If you refer to an older thread, please give a link. No need to repeat everything from scratch.

#4

It seems that you have chosen a single digit denominator in your formatting of the cell. To see a more accurate fraction allow more digits for the denominator.

It is the same for decimals, force just one decimal place then you will get 0.1

Anyway, it is for display only, underneath you still have 15 significant places for calculation

#5

@swissbusinet

Attach an example sheet showing

  • the actual formattings you suppose to be wrong / bad / misleading and explain (in the sheet) your reasons in every case.
  • the formatted results (as strings) like Excel (which version?) would show them. Don’t miss to also deliver the format codes.
  • the results as you would prefer them.

You should also be precise about what you mean by “older versions”.

If possible at all you should also provide a link to what you meant by

If not pssible you should tell why.
Below this level of precision (at least) a serious discussion may be impossible.

You may want to read about tdf#99996.

There are already much too many quasi standards where explicit rational standards should be regarded.
In this case the concept of Calc ist to to round to the nearest approximation that can be achieved regarding the setting described by the format code. What that will be is not decided by “industry standards” but by mathematics.
Give examples for cases where this concept is violated by Calc.

2 Likes
#6

@swissbusinet, you have the number 1/12 in cell. Setting the format ?/? you ask the program to find a simple fraction x/y (x and y are integers, y<=9), which has a minimum difference from 1/12.
Calc answer: 1/9, Excel answer: 0.
Which number is closest to 1/12?

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.