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

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.

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. Feb 4, 2020 at 17:08
• 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 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.