Below you can see my data (it's the German version, so the decimal point is a comma), already with the following conditional formatting rule applied: =B21>=B22; it is applied to rows 21 to 28 (29 is excluded).

The intention is to highlight all cells in which the number doesn't get smaller compared to the cell below it. Looks good.

enter image description here

Now I want to add another condition: I also want to highlight cells where it decreases by more than 0.25 compared to the cell below. I use this formula: =RUNDEN((B21-B22);2)<-0,25, which in the English version should be =Round((B21-B22),2)<-0.25. It leads to the following result:

enter image description here

Now I'd like to combine these two with an OR condition. This should combine the two pictures, right? It doesn't work, though. My condition is =ODER(B21>=B22;RUNDEN((B21-B22);2)<-0,25) and in English that's =OR(B21>=B22,ROUND((B21-B22),2)<-0.25). The result looks like

enter image description here

So all the cells with a "-" in them aren't highlighted. That's not an issue, in fact, that's what I want, but I don't see why this happens. Which means I don't completely understand what OR is doing here or what mistake I made. Since I need to apply this to a lot of other sheets, I'd like to understand what is happening here.

2 Answers 2


That's because B26-B27 becomes to 3 - '-' that results into #VALUE error. And that's not TRUE, so formatting not applied. In other cells by analogy.

  • So in general with an OR statement, when one of the conditions returns an error, the whole statement won't work?
    – Alex
    Feb 4, 2020 at 16:59
  • No, that is the case anyway - if any component of the formula returns error the total result is an error, i.e. if not using IFERROR, IFNA functions.
    – basic
    Feb 4, 2020 at 17:08
  • 1
    If the number formatting is "Comma Style" then 0 (zero) shows as - dash, which would not make an error in the formula. There you can do just @basic says but for a different reason, or you can put 0s (zeros) in the last line which would have the same effect without any change in formula.
    Feb 4, 2020 at 17:10
  • Of course, if the dash is just formatting and behind is a number, then there will be no errors
    – basic
    Feb 4, 2020 at 17:17

If I follow what you wish to achieve, the problem is in the ROUND() portion of the rule.

You wish to know if the cell above decreases by more than 0.25. But what you are testing is whether it decreases by more than NEGATIVE 0.25. So it is only TRUE if you see it INCREASE.

B21-B22 is -3.25 which is < -0.25 so that half of the OR() is TRUE and the rule activates, highlighting cell B21. B24-B25 is -1.00 which is an increase (it rose so the number being subtracted is greater than what it is being subtracted from so the result of an INCREASE is a negative number and in this case, one definitely < -0.25) but since it is < -0.25, that half of the OR() gives TRUE and the cell highlights even though the value ROSE rather than FELL as you are looking to find.

To fix that you can do a couple things. A very simple one is to make the subtraction test against > 0.25. If it falls, the second number is lower so you have a positive result that is greater the more it fell. That is the goal so...

Another would be to reverse the subtraction. So B22-B21 instead. If the value falls going to B22, that will create a negative number which would then indeed be < -0.25 as the fall became greater.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .