Excel - Conditional Formatting: Getting Or-Condition to work?

Question

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.

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:

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

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.

Answer 1

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.

Answer 2

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.