Comparing 3 Cells

I need a formula to compare three cells in three columns, if 2 of the cells have the same value then I would like a cell in the fourth column to register a value of 1.
Such as; cell A1 = 7, A2 = 5, A3 =7 then A4 would register a 1. If none of values agree then cell A4 would register a 0.
Thanks you for any help you can offer.

Hi Cutthroat and welcome to the forum
In A4 put

=IF(OR(A1=B1,A1=C1,B1=C1),1,0)

Originally Posted by TW1965

trying to compare three cells.. A1, B1, C1. And placing the largest value in D1
thanks for any help!

. Hi TW1965,
. You should do what nigelbloomy says.
. But I was just doing a bit of late night lurking and just learnt coincidentally how to do this, so thought it would be OK to drop the solution in here

. So I take some arbitrary values in calls A1, B1, C1, and put the formula I just learnt in E1, and used that in a simple INDEX(__,___,__) formula in D1 to get what you want:-

. Here is the result

Table a): ( What you see finally in the Spreadsheet )

Using Excel 2007

-	A	B	C	D	E
1	7	5	3	7	1

Sheet1

Using Excel 2007

Row\Col	A	B	C	D	E
1	7	5	3	7	1

Sheet1

………………………………………………………………..
. Here is the formulas

Table b):

Using Excel 2007

Row\Col	D	E
1	=INDEX(A1:C1,1,SUM(--(A1:C1=MAX(A1:C1))*COLUMN(A:C)))	=SUM(--(A1:C1=MAX(A1:C1))*COLUMN(A:C))

Sheet1

. Credit goes to Speshul here
http://www.excelforum.com/tips-and-t...ulas-work.html
. For sharing a very nice simple explanation of the Array formula in E1

Alan

P.s.
. Don’t forget this is a “CSE” Curly bracket formula thing…

. To get it to work…
.1 ) copy ( Ctrl C ) the formula you want complete from the second above Table (Formula Table b) to clipboard
. 2) select ( click in ) the cell where the formula should go (D1)
. 3) Hit F2 or select ( click in ) the formula bar (To be on the safe side do both!! )
. 4) paste in the formula from the clipboard ( Ctrl V ) ( check that the formula includes a = at the start)
. 5) now you do the famous “CSE” . – That is hold down the keys Ctrl and Shift, and hit Enter.

Hi Cutthroat, if you thought the last formula was bad look at this one.

=IF(OR(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))=TRUE,0,IF(OR(A1=B1,A1=C1,B1=C1),1,0))

The above says just what you ordered. If any of the three cells are blank then give back a zero. If none of them are blank then do the formula given before.

My concern is with the ISBLANK function. I'm reading it may be new to Excel 2010 so I'm still working on a 2003 solution. Perhaps an upgrade is in order? I'll keep working... I have to find a list of 2003 functions.

I know that 2003 had the Len() function so try this

=IF(OR(LEN(A1)=0,LEN(B1)=0,LEN(C1)=0)=TRUE,0,IF(OR(A1=B1,A1=C1,B1=C1),1,0))

Thank you MarvinP the modified formula is working perfectly. I took full credit for the formula and my supervisor now thinks I'm a genius. I can only hope you will be available for my future career advancement.
I consider this post "solved", I don't know how to add that icon. Thank you again.

Edit your original post - press the green button below it. Then click on Go Advanced and edit the prefix.

@TW1965 You need to start a new post if you want to ask a new question.