Ask Your Question

Is it possible to have a very complex conditional format?

asked 2019-09-14 21:59:04 +0100

Bubbe gravatar image

updated 2020-08-09 20:17:25 +0100

Alex Kemp gravatar image

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:
image description

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: image description

inValue is the user input

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.

image description

edit retag flag offensive close merge delete


Have you considered using the decimal numbering system?

Schiavinatto gravatar imageSchiavinatto ( 2019-09-15 14:25:37 +0100 )edit

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.

Bubbe gravatar imageBubbe ( 2019-09-15 20:09:23 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-09-15 13:45:39 +0100

Lupp gravatar image

updated 2019-09-15 13:48:07 +0100

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.)

edit flag offensive delete link more


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

Bubbe gravatar imageBubbe ( 2019-09-15 20:06:45 +0100 )edit

answered 2019-09-15 13:21:50 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more


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.

Bubbe gravatar imageBubbe ( 2019-09-15 20:11:34 +0100 )edit
Login/Signup to Answer

Question Tools



Asked: 2019-09-14 21:59:04 +0100

Seen: 126 times

Last updated: Sep 15 '19