Calc: working with fractions

English
#1

I have some columns full of improper fractions.
To stop libreoffice from treating these as dates each is prefixed with ’ (apostrophy) eg '16/15

I want to do some math on these fractions but cannot work out how to convert them to decimals.
I have come up with the following formula

=LEFT(IF(LEFT(T37)=”’”,right(t37;len(t37)-1),t37);FIND("/";IF(LEFT(T37)=”’”,right(t37;len(t37)-1),t37))-1)/RIGHT(IF(LEFT(T37)=”’”,right(t37;len(t37)-1),t37);FIND("/";IF(LEFT(T37)=”’”,right(t37;len(t37)-1),t37))+1)

which has syntax errors.

Is this really what I have to do to convert '16/15 to 1.06666666?

If so can someone correct the syntax for me, please?

#2

The formula wizard helps debugging syntax errors.

What does =ISNUMBER(T37) return?

#3

You can assign a number format category “Fraction” to a cell before entering values. In this case, the apostrophe is not required, and Calc will treat cell values as fractions.

1 Like
#6

Thanks people.
For me Libreoffice seems to mangle fractions in one way or another no matter how I enter them. Especially improper fractions.

I am happy for them to be text, but I’m not sure how to do that,
although a cell displays 9/8 it is still a date and has a value of 44xxx.

I’ve come up with an amended formula to convert cell contents to decimal that seems to work:

=LEFT(IF(NOT(ISERROR(VALUE(LEFT(T37)))),T37,RIGHT(T37,LEN(T37)-1)),FIND("/",IF(NOT(ISERROR(VALUE(LEFT(T37)))),T37,RIGHT(T37,LEN(T37)-1)))-1)/RIGHT(IF(NOT(ISERROR(VALUE(LEFT(T37)))),T37,RIGHT(T37,LEN(T37)-1)),LEN(IF(NOT(ISERROR(VALUE(LEFT(T37)))),T37,RIGHT(T37,LEN(T37)-1)))-FIND("/",IF(NOT(ISERROR(VALUE(LEFT(T37)))),T37,RIGHT(T37,LEN(T37)-1))))

which is quite a mouthful.

I just thought there would be an easier way to enter fractions as text and to convert them to decimal.

#7

If you format cell A1 as a fraction, e.g. ??/16 and enter in it =pi() it will display 50/16 but that is only a representation of the number. The actual number is still in the cell and is used in calculations as can be seen if in cell B1 you enter =A1 and format that cell as General. So no mangling going on, other than the usual decimal to binary or back.

1 Like
#8

try this Formula:

=IFERROR(MID(T37;FIND("'";T37)+1;FIND("/";T37)-2) / MID(T37;FIND("/";T37)+1;99);T37)
#9

Thanks karolus,
I had to modify it but it is a lot simpler than what I had.

 =MID(T7,IFERROR(FIND("'",T7)+1,1),FIND("/",T7)-IFERROR(FIND("'",T7)+1,1))/MID(T7,FIND("/",T7)+1,99)
#10

Thanks EarnestAI,
That eliminates the problem at the root.

How do I mark it as the solution? … done.

#11

Input of “improper” fractions like 7/8 is recognized as date if your locale uses a M/D (or the like) Date Acceptance Pattern, so either go to Tools → Options → Language Settings → Languages and remove the M/D or D/M pattern, or enter the fraction including the zero integer part, so 0 7/8.

#4

Entering Fractions

2 Likes

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