EXACT comparison formula
 

I'm having trouble coming up with a formula to compare specific cells
in a row.

I have
A1 = TRUE
B1 = FALSE
C1 = FALSE
D1 = TRUE

and then
A3 = "R"
B3 = ""
C3 = "E"
D3 = "R"

I would like to have a TRUE/FALSE in column E representing the
evaluation of EXACT on that row; only comparing cells if Row 1 has a
TRUE in it.

Array entering =AND(EXACT(A3,A3:D3)) in D4 includes C3 and B3 in the
example above. How can I modify this formula?

Thanks

Does this do what you want?

=SUMPRODUCT(--(A1:D1=(EXACT(A3,A3:D3))))=COUNTA(A1:D1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
I'm having trouble coming up with a formula to compare specific cells
in a row.

I have
A1 = TRUE
B1 = FALSE
C1 = FALSE
D1 = TRUE

and then
A3 = "R"
B3 = ""
C3 = "E"
D3 = "R"

I would like to have a TRUE/FALSE in column E representing the
evaluation of EXACT on that row; only comparing cells if Row 1 has a
TRUE in it.

Array entering =AND(EXACT(A3,A3:D3)) in D4 includes C3 and B3 in the
example above. How can I modify this formula?

Thanks

Bob, Thanks much.. It's very close. Any ideas how to account for A3
being blank (having EXACT work with the first column that has TRUE in
A1)?

Okay, but it gets a tad more complex.

First add a range name, InsertNamedefine ... of FirstCol with a Refersto
value of

=MIN(IF($A$1:$D$1,COLUMN($A$1:$D$1)),257)

Then use this formula

=SUMPRODUCT(--(INDIRECT(CHAR(64+FirstCol)&"1:D1")=(EXACT(OFFSET( A3,,FirstCol
-1),INDIRECT(CHAR(64+FirstCol)&"3:D3")))))=COUNTA(I NDIRECT(CHAR(64+FirstCol)
&"1:D1"))

I am not convinced it works properly though in all cases. What should it
return in this case?

A B C D
1 False True True False
2
3 R R R

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Bob, Thanks much.. It's very close. Any ideas how to account for A3
being blank (having EXACT work with the first column that has TRUE in
A1)?