Ask Your Question

How can I add conditional formatting based on values being <, >, or between other cell values [closed]

asked 2018-12-10 02:55:32 +0100

FBachofner gravatar image

I have a "legend" on a Calc sheet with 5 different values in 5 cells.

for example:

A1 = 4.9
A2 = 5
A3 = 10
A4 = 15
A5 = 20

I want to apply conditional formatting to a range as follows (psuedo code):

condition 1: IF <A1
condition 2: IF >=A2 AND <A3
condition 3: IF >=A3 AND <A4
condition 4: IF >=A4 AND <A5
condition 5: IF >=A5

Unfortunately I am getting stuck entering any useful formula in the "formula-is" of each condition in the conditional formatting dialog.

Thanks in advance for any guidance.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by FBachofner
close date 2018-12-10 07:43:34.349962

1 Answer

Sort by » oldest newest most voted

answered 2018-12-10 06:55:51 +0100

robleyd gravatar image

You should be able to do this with Cell value is as per this image - source file is also attached. Note that your conditions do not specify a format for values equal or greater than A1 and less than A2.

Also note the order of the conditions, from largest values down.

image description


If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

edit flag offensive delete link more


@robleyd Thanks, that's awesome!

A1 is (for the moment) actually anything less then "5" (i.e. value at A2) -- I just wanted (initially) to have a value in the cell that made it obvious.

I was using "formula-is" -- first because I couldn't seem to reference a cell in the Cell value is field and second (more importantly) because I was trying to establish conditions between two values.

Your idea to invert the conditions from largest values down seems to address the issue perfectly.

Thanks so much!

FBachofner gravatar imageFBachofner ( 2018-12-10 07:39:45 +0100 )edit

sorry, inadequate karma to upvote

I hope others will, your solution is really elegant!

FBachofner gravatar imageFBachofner ( 2018-12-10 07:42:41 +0100 )edit

But not original! I just treated it like I would an IF or CASE statement in any language for the same values. Essentially

IF X GE 20 THEN Blue
ELSE Indigo

I learnt that very early in programming.

robleyd gravatar imagerobleyd ( 2018-12-10 08:30:19 +0100 )edit

Additionally, there are "less than or equal to" and "less than" conditions, that can also be used in this case, to resemble the original order closer.

Mike Kaganski gravatar imageMike Kaganski ( 2018-12-10 10:27:56 +0100 )edit


I didn't say "original," just elegant. ;-)

Adding to my embarrassment, the way I laid it out is pretty much exactly how any basic programming text lays out a CASE statement. Alas, when working in Calc (which is rare) I rarely think like a programmer -- as I usually just need the most basic of formulas.

The real frustration came, however, from not knowing how to reference the darn cells inside of the conditional text formatter. if I had just figured that out . . .

Thanks again!

FBachofner gravatar imageFBachofner ( 2018-12-18 04:35:45 +0100 )edit

@Mike Kaganski

The "less than or equal to" and "less than" conditions are, indeed, ultimately the way I "finished" my conditions. This seems a little easier to parse if / when we need to look at the logic again.

I had seen all these options, but referencing the cells was somehow not obvious to me.

Many/most times when you can reference other cells, Calc puts one of those "shrink this dialog" buttons (as there is to the right of the "cell range" entry. The fact this was not next to the condition had me confused.

Thanks again for your input.

FBachofner gravatar imageFBachofner ( 2018-12-18 04:41:00 +0100 )edit

Question Tools

1 follower


Asked: 2018-12-10 02:55:32 +0100

Seen: 32 times

Last updated: Dec 10 '18