[Solved] Input Line fraction or decimal?

Discuss the spreadsheet application
Post Reply
x-eption
Posts: 4
Joined: Sun Jul 25, 2021 10:04 pm

[Solved] Input Line fraction or decimal?

Post by x-eption »

Sometimes, when I enter a fraction in a cell formatted for fractions, the Input Line automatically displays the decimal equivalent. Sometimes the Input Line displays the fraction. What controls that, and how do I make one or the other the default?
Last edited by robleyd on Tue Aug 03, 2021 10:52 am, edited 2 times in total.
Reason: Add green tick
LibreOffice-Calc for Mac v 7.1.4.2
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Input Line fraction or decimal?

Post by RusselB »

Welcome to the forums.
The conversion you are referring to is via a complicated algorithm (I've looked at the code).
To put it simply, if the fraction can be easily displayed as a common fraction (eg: 1/2, 1/4) then it displays as a fraction.
If it can't, like 1/10 then it displays as a decimal.
I'm not sure as to the exact details regarding what the system considers to be valid for a fractional display and what doesn't.
The only way I know of to have one method displayed at all times is to enter numbers using decimal format, which has it's own complications (eg: 1/3)
In the long run, all fractions are converted to decimals, as that is what the computer can use for the mathematics.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Input Line fraction or decimal?

Post by Villeroy »

LO 7.1.5
Number format # ?/??
Input
1/2
1/4
1/8
1/10
1/11
23/43

In the formula bar they are all shown by their decimal value.
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
x-eption
Posts: 4
Joined: Sun Jul 25, 2021 10:04 pm

Re: Input Line fraction or decimal?

Post by x-eption »

Thank you, RusselB and Villeroy. I have fractions working, but there remains one glitch: Entering "5 ½" displays "50.5."

Here's what I've been trying to do: Photographers usually mount their prints on a board ("mount board"), and then overlay it with another board ("mat") that has an opening cut out to display the image. Calculating widths and heights of the borders is a stumbling block for many artists. I put together an Excel spreadsheet that lets users type-in the information that results in the correct borders and opening sizes.

For Mac users, I created an Apple Numbers version, which is very clunky, primarily because Numbers doesn't allow cell locking. I had to lock the entire sheet, and then add unlocked overlays for inputting.

I figured LibreOffice-Calc would open the Excel sheet with no problem, but it looks like fractions with whole numbers is (so far) an incompatibility.

It's available for free on my website here: https://www.kenschuster.com/mat-board-calculator.

Thanks again for you help.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Input Line fraction or decimal?

Post by Villeroy »

Enter 5.5. It will be displayed as 5 1/2
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
x-eption
Posts: 4
Joined: Sun Jul 25, 2021 10:04 pm

Re: Input Line fraction or decimal?

Post by x-eption »

Villeroy wrote:Enter 5.5. It will be displayed as 5 1/2
Right, but first the fraction has to converted to the decimal before entering it. I'm trying to eliminate that extra step, as it does seamlessly in Excel.
LibreOffice-Calc for Mac v 7.1.4.2
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Input Line fraction or decimal?

Post by RusselB »

I just opened a new spreadsheet.
Formatted a cell as suggested, then entered 5 1/2
It displayed in the grid as expected and showed as 5.5 in the Edit line
Thus you can enter a fraction, even a mixed fraction, which is what 5 1/2 is, and it will display in the spreadsheet as the mixed fraction.
The only place you might see the difference, is in the actual Edit line, where it does the conversion to a decimal automatically.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Input Line fraction or decimal?

Post by Villeroy »

It depends on the auto-correct options. You have to turn off replacements like 1/2 --> ½

Work-around: 5{space}1/2{space} [Ctrl+Z] {Enter]
[Ctrl+Z] will undo the auto-correct replacement
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Input Line fraction or decimal?

Post by Villeroy »

I noticed an interesting issue with that file. The xlsx weighs 17 kB. I saved it as *.ods which weighs 600 kB. When I unmerge column A (the column is one merged cell), the file size shrinks from 600 kB to 22 kB. One more reason why merged cells can be evil.
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
x-eption
Posts: 4
Joined: Sun Jul 25, 2021 10:04 pm

Re: Input Line fraction or decimal?

Post by x-eption »

Thank you again for your interest in helping me find a solution to my problem, but I just couldn't get it to work like the Excel version. However, I came up with an inelegant solution. Instead of the user entering "5 ½" in one cell (which results in "50.5"), I ask users to enter the whole number in one cell, and the fraction in the adjacent cell. Then I made a formula that combines the two values (as decimals, of course), and then does the math that includes results of a previous entry, and that displays the fraction equivalent in the results cell. I didn't think it would work, but I'm pleasantly surprised. An alternative method would have been for the user to first convert the fraction to a decimal, add the whole number, and then enter that in the one cell. That would make the two-cell entry unnecessary, but requires the extra step. I made it available for download from my website today, as an ODS file, and haven't received any complaints yet. I invite you to have a look, if you wish. https://www.kenschuster.com/mat-board-calculator
LibreOffice-Calc for Mac v 7.1.4.2
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Input Line fraction or decimal?

Post by Lupp »

I wouldn't think Excel. Fractional number formats were reworked in LibreOffice to a good standard.

If you accept the advice by "Villeroy" to switch off outdated replacements in AutoCorrect Options... a recent LibreOffice will accept and correctly recognize "5 1/2" as wanted, and display it in the ordinary fraction number format (code # ?/?) even if the cell's number format was 'General' in advance. The disadvantage is that denominators are restricted to 1 digit then.

To allow for a more flexible fractional formatting I would suggest to set a format code like "# ?/?##" in advance for concerned cells.
You can NOT get the enterd value shown as an ordinary fraction in the formula bar or in the cell's area. While being edited it always is shown in a decimal format.

However: Instead of resorting to a second column for the fractional part, you can tell the users to enter (mixed) fractional numbers as formulas like =5 + 3/4". Two keystrokes more - and no ambiguity at all. They also can edit the formulas in an obvious way then.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply