Is it possible to have a very complex conditional format?

[Bubbe]

I often use Calc for my sewing. A lot of my projects only vary by dimension, but the cutting, layout and assembly is all the same. That being said, my values tend to be in fractions. However, a single fraction format does not work for me because sometimes a value can be multiples of 1/16, 1/8, 1/4 or 1/2. I want the formatting to reflect the appropriate formatting.

Consider this:

I have created a formula (below) which correctly identifies the desired formatting based on the user input (in the image it’s the last cell at the bottom.)

The question is: how can I apply this to the field where the entry is being made (where it’s labeled “Enter Value”)?

My formula:

where:

inValue is the user input

x4th=100

x8th=1000

x16th=10000

Realized afterwards an example of what I’m doing may be useful in understanding what’s going on. The spreadsheet image below is for making a cover for a spiral notebook with an attached pencil pouch.

The values wanted are entered in the yellow cells with red text. It’s one of the places I would also want to apply conditional formatting and not have to edit by hand every time.

[schiavinatto]

Have you considered using the decimal numbering system?

[Bubbe]

Thank you, but rulers don’t display/use the decimal system. They are marked in fractional increments and when I’m cutting my fabric, I’m referring to my worksheet for the amount to cut and using my ruler to measure off that distance.

[mariosv]

In the conditional format, the formula result, it’s the fulfillment of the condition, no to set up the format. If the condition it’s true then the format below it’s applied.
Create styles with the needed format (one for ‘# ?/2’, one for ‘# ?/4’, and so on), then set up a condition for every one in the necessary order, with their style selected.

[Bubbe]

I’m not certain I understand your reply. Are you saying the formula I wrote can be entered (piecemeal) in a conditional format definition? I think I tried that but am uncertain. I’ll look and try again as it would be useful to know it can be done.

[Lupp]

Why shouldn’t the fornat code # ?/?? work for you to get what you want? It works for me. And with a third question mark for the denominator the code also works with /128, /256, 512. This should be enough even in extreme cases of denominators restriczd to powers of 2.

If you want to get a different format in case of the above code not applying exactly, you only need one condition in CF like A1*512<>INT(A1*512) assuming your CF range is starting at A1.

There is an issue with fractional formats, however, slightly spoiling the results when using the above given code: The alignment is tried as if the number of digits in the denominator always is equal to the number of question marks even if less digits are needed, but this is done with the help of trailing spaces, and if not a fix-width font is used, the width of a space does not match the width of a digit. (At least I myself tend to judge this to be a mistake, and would prefer to abandon this kind of alignment completely.)

[Bubbe]

Thank you. I have no idea how I missed such a simple solution. Just tried it and it worked handsomely!