Ask Your Question

Conditional formatting not working properly [closed]

asked 2018-12-02 17:13:11 +0200

mattia.b89 gravatar image

updated 2020-08-09 20:13:42 +0200

Alex Kemp gravatar image

Hi guys, I'd like to color TWO cells if ONE of them has a certain kind of value.

I created an example in which I want to color cells corresponding to "check" & "perc. diff." rows.

IF the percentage error (perc. diff.) is below 5% I want them all GREEN
WHILE if it is greater or equal to 5%, I want them RED


Unfortunately, as I've done in the sheet above, it ONLY works PARTIALLY

What's wrong? Is there any other way to achieve my goal?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-12-04 15:19:34.119844

1 Answer

Sort by » oldest newest most voted

answered 2018-12-02 17:28:56 +0200

JohnSUN gravatar image

Just use dollar sign for row number in cell address (use absolute address of row)


(Also remove the extra letter B in the function name in the second condition - "ABS", not "abbs")

edit flag offensive delete link more


Ok, now it works! But why do I need the dollar sign '$'?

mattia.b89 gravatar imagemattia.b89 ( 2018-12-03 23:42:28 +0200 )edit

Match the formula and the range to which it applies. The range includes two cells C5 and C6, the formula uses the value of one cell C6. The relative address means that the value of cell C6 will be used to calculate the color of cell C5, and - it is important! - to calculate the color of cell C6 will be used the value of the cell with a shift, the value of cell C7. To avoid this shift, the line number must be motionless. The dollar sign does exactly that.

JohnSUN gravatar imageJohnSUN ( 2018-12-04 06:56:59 +0200 )edit

Ok, thus Calc doesn't apply the same Condition for EACH cell in the range but it evaluates cell-by-cell, just like for any other "normal" cell in the sheet... One question more: what happens if I block (using $) cells in the range. e.g. instead C5:C6 -> $C$5:$C$6 ???

mattia.b89 gravatar imagemattia.b89 ( 2018-12-04 21:54:09 +0200 )edit

Just try to do it and make sure nothing happens. Calc will not save this change. If this happened, this conditional format would be impossible to copy to other cells.

JohnSUN gravatar imageJohnSUN ( 2018-12-05 07:40:58 +0200 )edit

Question Tools

1 follower


Asked: 2018-12-02 17:13:11 +0200

Seen: 363 times

Last updated: Dec 03 '18