EXACT comparison formula


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
 
Ad

Advertisements

B

Bob Phillips

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)
 
T

test.file

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)?
 
Ad

Advertisements

B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top