Conditional formatting formula error

Question

I was following the tutorial on this page to do some conditional formatting, but I'm getting an error when using this formula

=AND ($B3>=45, $B3<=54)

Error message:

enter image description here

It says there is a problem with the formula, and it explains if it starts with an = or - sign it means it's a formula... The information on the error doesn't say anything helpful. Not used to work on excel so sorry if this is stupid. Can you help me? Thanks

Does $B3 have actual numerical data in it? Please post a reproducible question. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 16, 2018 at 9:39
Enter =AND(TRUE, TRUE) into any cell. Do you still get an error? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 16, 2018 at 9:54
@Petermonteer, I would always recommend trying the formula on the worksheet before using it in conditional formatting. Check that it returns TRUE where you expect it to? — CallumDA
– CallumDA, Commented Feb 16, 2018 at 10:18

CallumDA · Accepted Answer · 2018-02-16 10:27:05Z

5

Firstly, try changing your comma , to a semicolon ;.

=AND($B3>=45; $B3<=54)

As pointed out by IQV, you will also need to use the Portugese version of AND() which is E():

=E($B3>=45; $B3<=54)

Here is a source for translating English functions to Portugese functions:
http://dolf.trieschnigg.nl/excel/index.php?langids=en+po

Your regional settings determine whether you need to use a comma or semicolon as a separator. So, when you find formulas with commas in future you'll need to make the same changes again.

You could also change your list separator in your control panel. Go to Clock, Language and Region > Region and Language > Additional settings > List separator and change it from ; to ,.

For more details, see here:
http://datapigtechnologies.com/blog/index.php/why-your-version-of-excel-only-accept-semicolons-in-formulas-instead-of-commas/.

edited Feb 16, 2018 at 10:27

answered Feb 16, 2018 at 10:01

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim Biegeleisen Over a year ago

I was not aware that regional settings influenced Excel syntax. Most programming languages do not have this +1.

IQV Over a year ago

But it's strange, that the localized function name should not work. It would be interesting, if localized function name E() and ; would work too. In german Excel one can use UND() in conditional formatting.

CallumDA Over a year ago

@IQV, I thought that was much more likely to be the problem as well. Perhaps Excel allows local language and English?

IQV Over a year ago

@CallumDA I tried it out. In german version it doesn't work. Only german function names work. It's surprising and strange...

Petermonteer Over a year ago

You were right! When changing to ; it accepted the formula but it didn't work, I had to change to E() so it works! Thanks!

IQV · Accepted Answer · 2018-02-16 10:27:57Z

2

Obviously you use a localized excel. So in conditional formatting you have to use the localized function names. When using E() instead of AND() it should work.

And as CallumDA mentioned in the comments you should prove which list separator you have to use in your formulas: ; or ,.

edited Feb 16, 2018 at 10:27

answered Feb 16, 2018 at 9:47

IQV

5006 silver badges14 bronze badges

Collectives™ on Stack Overflow

Conditional formatting formula error

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related