I was having a look at this question, and in order to help, I wrote a conditional formatting rule, based on this formula:
="ABS(B3-A3)>=1,5" // the double-quotes are automatically added by Excel
This, however, seems not to work, although the formula seems to be correct (in the cell at the right, I asked whether this was true and I got a positive result, as you can see (WAAR
is Dutch for true)):
I'm not interested in the final solution, I just want to know how I can debug such a situation: apparantly the formula is true, but still the conditional formatting is not applied. This might mean that:
How can I, step by step, evaluate the formula, entered in a conditional formatting configuration, in order to see what might be wrong?
Thanks in advance
Sorry, guys, but the truth is really nerve-wrecking:
So what's the deal?
Apparently there seems to be a problem with the decimal separator : when I enter a dot, using Excel regularly, it gets converted into a comma. When I enter a dot in a conditional formatting formula, this conversion is not done, resulting in a wrong formula, where Excel automatically adds double quotes. As the double quotes result in a wrong formula, the conditional formatting won't be applied.
So:
From the moment you see double quotes in a conditional formula, this is an indication that something's wrong, and that your conditional formatting won't work!