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

[FBachofner]

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.

[robleyd]

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.

cond_format.ods

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.

[FBachofner]

@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]

sorry, inadequate karma to upvote

I hope others will, your solution is really elegant!

[robleyd]

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
ELSEIF X GE  15 THEN Red
ELSEIF X GE 10 THEN Green
ELSEIF X GE 5 THEN Orange
ELSE Indigo
ENDIF

I learnt that very early in programming.

[mikekaganski]

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.

[FBachofner]

@robleyd

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]

@mikekaganski

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.