FORMULA CHALLENGE IV-WHO IS THE WINNER?

[apolloh]

Formula Challenge I:
http://www.mrexcel.com/board2/viewtopic.php?t=156081
Formula Challenge II:
http://www.mrexcel.com/board2/viewtopic.php?t=160246
Formula Challenge III:
http://www.mrexcel.com/board2/viewtopic.php?t=160278

There is a very interest and challengable topic copied from the competition forum at http://club.excelhome.net. Guys, let's work it out at the same time.

CLG4.xls
	A	B	C	D	E	F	G	H	I
1	WHO IS THE WINNER?
2		FORMULA CHALLENGE
3		I	II	III	IV	V			Result
4	Aladin	4	5	4	4	4		Domenic	13
5	Apolloh	5	2	1	1	3		Aladin	12
6	Barrie	4	3	4	4	2		tusharm	11
7	barry	5	3	5	1	2		Emily	11
8	bosco_yip	3	3	3	5	1		Barrie	11
9	Domenic	5	5	3	5	1		Teacher	10
10	Emily	4	5	1	4	3		malcom	10
11	Erik	4	1	1	4	5		Jones	10
12	Jay	2	5	1	2	5		barry	10
13	Jones	4	1	4	2	4		Jay	9
14	malcom	5	5	2	3	1		Erik	9
15	Teacher	2	4	4	4	1		bosco_yip	9
16	tusharm	2	5	5	4	1		UCSDKID	8
17	UCSDKID	1	1	3	4	4		Apolloh	6
18
19	Result=SUM(I,II,III,IV,V)-MIN(I,II,II,IV,V)-MAX(I,II,III,IV,V)
20	Write your formula for the H3:I17 to gain the winner.
Sheet1

Only one rule:
One formula for H3,another one for I3,copied down.

There may be more than one type of winner, but I propose the following 5 categories.
A. Most Transparent
B. Most Flexible
C. Most Efficient
D. Shortest (least number of characters)
E. Earliest

 

[fairwinds]

Hi,

H4:
=LOOKUP(2,1/(((SUBTOTAL(9,OFFSET($B$3:$F$3,ROW(INDIRECT("1:14")),0))-SUBTOTAL(4,OFFSET($B$3:$F$3,ROW(INDIRECT("1:14")),0))-SUBTOTAL(5,OFFSET($B$3:$F$3,ROW(INDIRECT("1:14")),0)))=I4)*ISNA(MATCH($A$4:$A$17,$H$3:H3,0))),$A$4:$A$17)
Normal enter and dragged down


I4:
=LARGE(SUBTOTAL(9,OFFSET($B$3:$F$3,ROW(INDIRECT("1:14")),0))-SUBTOTAL(4,OFFSET($B$3:$F$3,ROW(INDIRECT("1:14")),0))-SUBTOTAL(5,OFFSET($B$3:$F$3,ROW(INDIRECT("1:14")),0)),ROW()-ROW($I$3))
Ctrl + shift + enter and dragged down

Book1
	A	B	C	D	E	F	G	H	I	J
1	WHO IS THE WINNER?
2		FORMULA CHALLENGE
3		I	II	III	IV	V			Result
4	Aladin	4	5	4	4	4		Domenic	13
5	Apolloh	5	2	1	1	3		Aladin	12
6	Barrie	4	3	4	4	2		tusharm	11
7	barry	5	3	5	1	2		Emily	11
8	bosco_yip	3	3	3	5	1		Barrie	11
9	Domenic	5	5	3	5	1		Teacher	10
10	Emily	4	5	1	4	3		malcom	10
11	Erik	4	1	1	4	5		Jones	10
12	Jay	2	5	1	2	5		barry	10
13	Jones	4	1	4	2	4		Jay	9
14	malcom	5	5	2	3	1		Erik	9
15	Teacher	2	4	4	4	1		bosco_yip	9
16	tusharm	2	5	5	4	1		UCSDKID	8
17	UCSDKID	1	1	3	4	4		Apolloh	6
18
Sheet1

 

[apolloh]

Hi,fairwinds

Thanks,your formula do work excellently.

Can we make it shorter or more efficient...?

 

[Domenic]

With a little help from fairwinds, here's my shot at it...


H4, copied down:

=LOOKUP(2,1/((MMULT(SUBTOTAL({9,4,5},OFFSET($B$4:$F$17,ROW($B$4:$F$17)-ROW($B$4),0,1)),{1;-1;-1})=I4)*(ISNA(MATCH($A$4:$A$17,$H$3:H3,0)))),$A$4:$A$17)


I4, copied down:

=LARGE(MMULT(SUBTOTAL({9,4,5},OFFSET($B$4:$F$17,ROW($B$4:$F$17)-ROW($B$4),0,1)),{1;-1;-1}),ROWS($I$4:I4))

 

[fairwinds]

SUBTOTAL({9,4,5},

Cool.

I've never though of even try that way.

 

[apolloh]

Yeh，Domenic

It is outstanding!

Let's continue...

Would anybody like to create a result as a array in the remember? Sometimes it is useful to graph a chart or finish a query without any helper range of cells.

 

[apolloh]

Would anyone like to try other soluton for this topic?

 

[apolloh]

A new solution not in using "subtotal" function.

H4

=LOOKUP(2,1/((MMULT((ABS(RANK($B$4:$F$17,OFFSET($B$4:$F$4,ROW($B$4:$B$17)-ROW($B$4),))+COUNTIF(OFFSET($B$4,ROW($B$4:$B$17)-ROW($B$4),,,COLUMN($B$4:$F$4)-COLUMN($B$4)+1),$B$4:$F$17)-4)<2)*$B$4:$F$17,{1;1;1;1;1})=I4)*(COUNTIF($H$3:H3,$A$4:$A$17)=0)),$A$4:$A$17)

I4

=LARGE(MMULT((ABS(RANK($B$4:$F$17,OFFSET($B$4:$F$4,ROW($B$4:$B$17)-ROW($B$4),))+COUNTIF(OFFSET($B$4,ROW($B$4:$B$17)-ROW($B$4),,,COLUMN($B$4:$F$4)-COLUMN($B$4)+1),$B$4:$F$17)-4)<2)*$B$4:$F$17,{1;1;1;1;1}),ROWS($I$4:$I4))