How to format with woodworking fractions?

[Auseklis]

I am a woodworker who must work in Imperial measurements, including standard fractions. I understand how to format numbers to display in 32nds, but I also need the resulting fraction to display the LCD (least common denominator) version of the answer.

For example, by using # ??/32 for number formatting, then a value of 0.53125 displays correctly as 17/32.

However, the value of 0.125 displays as 4/32 where I need it to display the LCD version of 1/8.

While using # ??/?? would display .125 as the desired 1/8, it would also display 0.3 as 3/10 which is unusable for me. I need 0.3 to display as 5/16 (3/10 = 9.6/32 which rounds to 10/32 whose LCD is 5/16)

So, to be clear, I can only use answers that are # ??/2, # ??/4, # ??/8, # ??/16, or # ??/32 --whichever is the LCD version of the fraction.

I thought about rounding answers to the nearest 1/32 with =ROUND(_____*32,0)/32, then using the typical # ??/?? formatting so that the answer would be given in the LCD version–but this can cause too much “drift” in the sums of measurements when many rounded answers are totaled together.

For example, 50 measurements of 0.3 inches should equal 15 inches (50*0.3 = 15), but if I use the rounding method to display my answers in 32nds, it totals 15-5/8 inches (50*ROUND(3/10*32,0)/32 = 15.625). Thus, I just want the displayed value to be rounded to the nearest 32nd–I don’t want to round the actual value of the cell.

For my Metric friends, here is a standard woodworking ruler-which leads to my quandary…

[ajlittoz]

Use intermediate cells/columns/rows.

Have your calculations in a “math” area of the spreadsheet. Here your results are as accurate as you require it.

In a specific row/column, you copy the results to display with =ROUND(…*32,0)/32.

In the “display” area of your spreadsheet, you reference the rounded value and you format the cell ??/??.

It is always a cute trick to separate calculations from display, just like you would use a scratchpad when computing with a pencil and copying the result into an invoice.

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.

[Auseklis]

I appreciate the answer, one which I thought of as well. The reason I posed the question is because I would prefer a solution over a work-around. If there is no existing solution within the “format” functionality, then perhaps someone could point me towards the section of code where formatting is defined–and I can work on a coded solution to contribute to the code base.

Since I have seen similar questions in other forums and since the US–the world’s largest economy–measures all of its architecture and building products in Imperial units, i.e., 3’ 2-1/4" (three feet, two and one-quarter inches), it just seems that there should be a built-in solution. I only mention economic size as an indicator of the “not trivial” nature of the market need for the solution.

[Lupp]

(OT) US may be “the worlds largest economy” for another few years. Their stubborn sticking to “imperial units”, their disreagarding of SI in many cases, their forcing outdated paper sizes and absurd date formatting partly on “chummy economies”… is an arrogation, imo.
If LibO actually should implement formatting in feet / inch in Calc, I will retire from support.

Concerning fractions: You may post a feature request (https://bugs.documentfoundation.org) to introduce a special “woodworker” series of fractional formats. I don’t think it can be done without breaking the whole thing.

Your very special needs should (without additional rounding) be satisfied by ConditionaFormatting based on a few cell styles. See attached example.
ask305761specialSeriesOfFractinalFormatsPowersOfTwoDenominators.ods

Also see: Developers | LibreOffice - Free Office Suite - Based on OpenOffice - Compatible with Microsoft

[ajlittoz]

@Lupp: Officially, the US passed the Metric Act in 1866 and signed the Convention du Mètre in 1875 in Paris. Since then, these commitments weren’t translated in concrete facts for day to day life. Imperial units confusion even caused the loss of a space probe. And this is no day-to-day routine, it’s supposed to be science!

[Lupp]

That’s exactly the facts I have in mind when I crticize US (and partly UK) stubbornness and retardation (insofar). I don’t want to reciprocate arrogation. However I really feel uneasy with a tendency to reestablish outdated units in fields of modern technique. We still suffer from (at that time!) clever inventions for Mesopotamian astronomy 3300+ years ago because of our inaptness to change old habits.
The case of “Imperial Units”, however, is much worse. The system (1824) never was clever, but was created (imo) as a contribution to British imperialism when the concept of the metric system already existed (1790 +) and had proven to be much better.
Let’s have another Tea Party.

(Addendum:) I don’t criticize the usage of vulgar fractions. They specifically are a valuable subject to base parts of mathematical education on, even if their everyday applicability became doubtable.
If it can be done without breaking the concept a 2^n-denominator formatting is a good idea.

[ajlittoz]

And a 2ⁿ denominator matches more closely computer floating point numbers

To add more to the controversy, UK accepted SI in 1884 when the international meridian was swapped between Paris and Greenwich. This was part of the deal. But treaties bind only those who respect them.

[Lupp]

See tdf#141870

But treaties bind only those who respect them.

You had a talk with little Boris? What he told me last time was that others are expected to respect treaties in every case.

And a section of my steel ruler (down to the 64ths of an inch):