EXACT comparison formula

  • Thread starter Thread starter test.file
  • Start date Start date
T

test.file

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)
 
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, Insert>Name>define ... 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(INDIRECT(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)
 
Back
Top