Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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)? |
#4
|
|||
|
|||
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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dragging a formula | Excel Discussion (Misc queries) | |||
EXACT formula | Excel Discussion (Misc queries) | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Use of the DSUM formula to find exact matches in datatables | Excel Worksheet Functions |