How to deal with double quotes in conditional formatting's formula

1
votes

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)):

Excel screenshot

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:

  • Something is wrong with the language settings.
  • Something is wrong with the dot/comma decimal separator.
  • Something else is wrong with the formula (absolute references, relative references, ...)

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

3
exceldebuggingconditional-formattingdouble-quotesdecimal-point
For what range is the formula applied? – basic
First I did it for the four cells in B column, but as this did not work, I tried it solely for cell B3 (which still did not work out). – Dominique

3 Answers

1
votes
  1. Select the whole range (for the example A2:B5)
  2. Add in conditional formatting formula =ABS($B3-$A3)>=1.5

enter image description here

Result:

enter image description here

1
votes

Remove the "" at the beginning and end of your formula. Should do the trick.

1
votes

Sorry, guys, but the truth is really nerve-wrecking:

  • The answer of Error 1004 is correct. Thanks for the screenshot.
  • The answer of Jario is correct too.

So what's the deal?

Apparantly 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 formattng 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!