Formula problem with Excel 2010

[mns]

Hi there,
I am trying to enter this formula in conditional formatting window
but my Excel 2010 wont accept it. I get the error "the formula you
entered contains errors".
I tried the formula as
=AND(H3=1,I3=1) and =AND($H$3=1,$I$3=1) doesnt matter.
But when I try it like ="AND(H3=1,I3=1)" Excel accepts this formula
but does nothing
My range is =$A$3:$I$3. I cant think of any solutions. help please.

 

[Jacob Skaria]

Do you mean

=IF(AND(H3=1,I3=1),"True","False")

 

[mns]

Jacob, thanks for replay. I cannot enter that formula too.
Let me underline where I enter the formula again.

I select a range of cells on a row. (=$A$3:$I$3)
Then I hit conditional formatting and add a new rule.
I select Use a formula to determine which cells to format.
then I cannot enter anything like =IF(AND(H3=1,I3=1),"True","False") this.
Somehow excel says formula contain errors. If I edit my formula like
="IF(AND(H3=1,I3=1),"True","False")" then Excel accepts it but nothing
changes.

note that if I enter =H3=1 then it checks for that cell for that value and
if its
ture some coloring happens(as i arranged). cant enter abit more compex
formulas

 

[Bob Phillips]

The IF is totally unnecessary, =AND(H3=1,I3=1) returns TRUE or FALSE which
is what CF requires.

 

[Jacob Skaria]

Oops.. I missed that it was conditional formatting..

--Select the range A3:I3
--Enter the below formula to format the selected range if H3 and I3 = 1
=AND($H3=1,$I3=1)

 

[Bob Phillips]

I just tried this in 2010 and it worked fine. I have seen this before, in
some previous Excel version, but I cannot recall the circumstances. If I
remember, I will post back.

 

[mns]

yes thank you guys but the problem is i cannot use
=AND($H3=1,$I3=1). Thats what is the problem excel says there is an error.
I am using office 2010

i can enter formula if i type like ="AND($H3=1,$I3=1)" this. This is my
problem

 

[Bob Phillips]

See my other response.

--

HTH

Bob

yes thank you guys but the problem is i cannot use
=AND($H3=1,$I3=1). Thats what is the problem excel says there is an
error.
I am using office 2010

i can enter formula if i type like ="AND($H3=1,$I3=1)" this. This is my
problem

 

[Jacob Skaria]

I didnt notice that the query is on CF..and thought the OP is trying to
return a text string from the condition....

 

[Bob Phillips]

Even if it is not CF, the IF is redundant.

 

[ker_01]

mns-

You mention that you can enter CF on single cells and have it work as
expected. I don't use 2010, but I suspect this will work on any version.

Select cell A3 (single cell only)
Put in your CF formula: =AND($H3=1,$I3=1) along with your desired formatting
Select cell A3 and click on format painter
Select row 3 (and additional rows, if desired)

HTH,
Keith

 

[David Biddulph]

If it objecting to those formulae, is it highlighting the comma? Perhaps
your Windows Regional Options (in Control Panel, not Excel) have specified
your list separator as a semi-colon instead of a comma?

 

[Jacob Skaria]

Do you mean to return a text string ??

 

[Bob Phillips]

No, I mean to return TRUE or FALSE as your formula did.