Conditional Formatting Based on Multiple Variables - Formula Failing to Register

schaha

New Member
Joined
Jun 8, 2016
Messages
5
Hello,

I have been working in a spreadsheet with a really large data set and i need to identify a set based on 2 variables - not date related. Everything I have seen so far has a date in it and I have tried to copy those formulas, but get the error that says, "are you trying to create a formula?" Excel seems to see the =and then not recognize my input.

I tried to use a formula copying this:
=IF(AND(B1="NO",R1=>TODAY()+10))

and I tried to translate it to the following, which does NOT work:
=IF(AND(B1="E",R1=>"47500"))

Can anyone help me figure out what is wrong with my formula? Basically is the value in B is E and the value in R is below 47500, I need it to show me.

Thank you!!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi
Welcome to the board

THe syntax of IF() is

=IF(condition, value case TRUE, value case FALSE)

in your case you just have

=IF(condition)

which is a syntax error.

In this case, I think you don't need the IF(), just use

=AND( ... )
 
Upvote 0
I just tried it - still getting the same message:

"We found a problem with your formula. Try inserting a function on the formulas tab or click help..."

What do you think?
 
Upvote 0
So i tried =AND(N:N="E",O:O>="47500")) and I get the same error... am I misunderstanding what it should be written as? Sorry and thanks for your help.
 
Upvote 0
This is a simple example that I just tried.

I want the cell A3 to be painted in red if the cell N3 (a text) is equal to "E" and the cell O3 (a date) is bigger or equal to today's date + 10

In A3, in the conditional formatting formula I used:

=AND(N3="E",O3>=TODAY()+10)


Does this example help?
 
Upvote 0
So that is the example I found and originally tried to copy for my formula. I was trying to format the column of N, and then I thought maybe it wasn't recognizing it because it was in that column and there was a condition there, but even if i select column A and copy that formula basically to format with the formula =AND(N3="E",O3>="47500"), excel still errors out and doesn't recognize the formula.. not sure what to do at this point..

thanks for your help with all this though.

This is a simple example that I just tried.

I want the cell A3 to be painted in red if the cell N3 (a text) is equal to "E" and the cell O3 (a date) is bigger or equal to today's date + 10

In A3, in the conditional formatting formula I used:

=AND(N3="E",O3>=TODAY()+10)


Does this example help?
 
Upvote 0
Hi

1 - I selected A3:A20, used in the coditional formatting the formula that I posted before and all the cells A3:A20 assumed the format OK

2 - Do you have the English installation of excel? in some countries you separate the function parameters with a semicolon ";" instead of a comma ","

Also you should use:

=AND(N3="E",O3>=47500)

not "47500", you don't want to compare a text, you want to compare a number.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top