SUMPRODUCT with IF, ELSE

[JDCupps]

Afternoon, I have a spreadsheet that I created for a confidence pool I manage. The spreadsheet cuts the football season in half and each half has a column for wins/half. Each cell in that column has a corresponding formula to that shown for K3. The formula works great except for the situation shown in Column G (week 6), A Tie.... While it's true that both players did indeed win that week, I'd be much happier if Column K were to show 1.5 for both users instead (god help me if we manage to have a 3-way tie). The cells in Column K are used later on in the spreadsheet to show winnings (or lack of winnings) for each player.
Is there a way of adjusting the formula to take into account the possibility of a tie? Although I'm relatively familiar with Excel as a whole, this type of formula (if possible) is way above my abilities and any help would be hugely appreciated.
Thanks

 

[CA_Punit]

Try this

Book1
	B	C	D	E	F	G	H	I	J	K	L
3		Week1	Week2	Week3	Week4	Week5	Week6			Formula
4	Bowlerx	XXX	262	182	194	166	158			2.33
5	Kdog	XXX	258	212	206	156	144			1.00
6	Squeaky	XXX	252	184	180	146	154			-
7	Jenne	XXX	214	144	170	98	138			-
8	Balke	XXX	240	182	200	146	158			0.33
9	Tater	XXX	230	210	168	140	146			-
10	Justsmile	XXX	222	177	192	139	112			-
11	Coolhand	XXX	0	174	162	142	120			-
12	Ginaforce	XXX	0	135	0	52	94			-
13	vaccination	XXX	258	159	168	159	158			0.33
14	Jerryfuton	XXX	234	165	212	164	106			1.00
15
Sheet1

Cell Formulas
Range	Formula
K4:K14	K4	=SUM(--IF(D4:H4=CHOOSE({1,2,3,4,5},MAX($D$4:$D$14),MAX($E$4:$E$14),MAX($F$4:$F$14),MAX($G$4:$G$14),MAX($H$4:$H$14)),CHOOSE({1,2,3,4,5},1/SUM(--($D$4:$D$14=MAX($D$4:$D$14))),1/SUM(--($E$4:$E$14=MAX($E$4:$E$14))),1/SUM(--($F$4:$F$14=MAX($F$4:$F$14))),1/SUM(--($G$4:$G$14=MAX($G$4:$G$14))),1/SUM(--($H$4:$H$14=MAX($H$4:$H$14))))))

 

[jasonb75]

Try this formula in K3

=SUM(IFERROR((RANK($C3:$J3,OFFSET($C$3,0,COLUMN($C$3:$J$3)-COLUMN($C$3),ROWS($C$3:$C$13),1),0)=1)/COUNTIF(OFFSET($C$3,0,COLUMN($C$3:$J$3)-COLUMN($C$3),ROWS($C$3:$C$13),1),$C3:$J3),0))

If your profile is showing the correct version of excel then you should have dynamic arrays, if not the formula will need to be array confirmed with ctrl shift enter.

 

[JDCupps]

Try this

Book1
	B	C	D	E	F	G	H	I	J	K	L
3		Week1	Week2	Week3	Week4	Week5	Week6			Formula
4	Bowlerx	XXX	262	182	194	166	158			2.33
5	Kdog	XXX	258	212	206	156	144			1.00
6	Squeaky	XXX	252	184	180	146	154			-
7	Jenne	XXX	214	144	170	98	138			-
8	Balke	XXX	240	182	200	146	158			0.33
9	Tater	XXX	230	210	168	140	146			-
10	Justsmile	XXX	222	177	192	139	112			-
11	Coolhand	XXX	0	174	162	142	120			-
12	Ginaforce	XXX	0	135	0	52	94			-
13	vaccination	XXX	258	159	168	159	158			0.33
14	Jerryfuton	XXX	234	165	212	164	106			1.00
15
Sheet1

Cell Formulas
Range	Formula
K4:K14	K4	=SUM(--IF(D4:H4=CHOOSE({1,2,3,4,5},MAX($D$4:$D$14),MAX($E$4:$E$14),MAX($F$4:$F$14),MAX($G$4:$G$14),MAX($H$4:$H$14)),CHOOSE({1,2,3,4,5},1/SUM(--($D$4:$D$14=MAX($D$4:$D$14))),1/SUM(--($E$4:$E$14=MAX($E$4:$E$14))),1/SUM(--($F$4:$F$14=MAX($F$4:$F$14))),1/SUM(--($G$4:$G$14=MAX($G$4:$G$14))),1/SUM(--($H$4:$H$14=MAX($H$4:$H$14))))))

CA-Punit, I had to alter the code to include the range of C3:J13 but it's giving me a value error. Not quite sure why. Maybe the blank cells for dates that haven't happened yet? Maybe because all the columns are conditionally formatted (although I can't see why that would matter) Here's how I changed the code:
=SUM(-IF(C3:J3=CHOOSE({1,2,3,4,5},MAX($C$3:$C$13),MAX($D$3:$D$13),MAX($E$3:$E$13),MAX($F$3:$F$13),MAX($G$3:$G$13),MAX($H$3:$H$13),MAX($I$3:$I$13),MAX($J$3:$J$13)),CHOOSE({1,2,3,4,5},1/SUM(--($C$3:$C$13=MAX($C$3:$C$13))),1/SUM(--($D$3:$D$13=MAX($D$3:$D$13))),1/SUM(--($E$3:$E$13=MAX($E$3:$E$13))),1/SUM(--($F$3:$F$13=MAX($F$3:$F$13))),1/SUM(--($G$3:$G$13=MAX($G$3:$G$13))),1/SUM(--($H$3:$H$13=MAX($H$3:$H$13))),1/SUM(--($I$3:$I$13=MAX($I$3:$I$13))),1/SUM(--($J$3:$J$13=MAX($J$3:$J$13))))))

 

[JDCupps]

Try this formula in K3

=SUM(IFERROR((RANK($C3:$J3,OFFSET($C$3,0,COLUMN($C$3:$J$3)-COLUMN($C$3),ROWS($C$3:$C$13),1),0)=1)/COUNTIF(OFFSET($C$3,0,COLUMN($C$3:$J$3)-COLUMN($C$3),ROWS($C$3:$C$13),1),$C3:$J3),0))

If your profile is showing the correct version of excel then you should have dynamic arrays, if not the formula will need to be array confirmed with ctrl shift enter.

Hey Jason. that worked great, with all I've tested so far. And actually a lot less clunky than the previous code I was using.
Thanks A Bunch!!!