FORMULA CHALLENGE IV-WHO IS THE WINNER?

apolloh

Board Regular
Joined
Jul 24, 2005
Messages
103
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
ABCDEFGHI
1WHO IS THE WINNER?
2FORMULA CHALLENGE
3IIIIIIIVVResult
4Aladin45444Domenic13
5Apolloh52113Aladin12
6Barrie43442tusharm11
7barry53512Emily11
8bosco_yip33351Barrie11
9Domenic55351Teacher10
10Emily45143malcom10
11Erik41145Jones10
12Jay25125barry10
13Jones41424Jay9
14malcom55231Erik9
15Teacher24441bosco_yip9
16tusharm25541UCSDKID8
17UCSDKID11344Apolloh6
18
19Result=SUM(I,II,III,IV,V)-MIN(I,II,II,IV,V)-MAX(I,II,III,IV,V)
20Write 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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDEFGHIJ
1WHO IS THE WINNER?
2FORMULA CHALLENGE
3IIIIIIIVVResult
4Aladin45444Domenic13
5Apolloh52113Aladin12
6Barrie43442tusharm11
7barry53512Emily11
8bosco_yip33351Barrie11
9Domenic55351Teacher10
10Emily45143malcom10
11Erik41145Jones10
12Jay25125barry10
13Jones41424Jay9
14malcom55231Erik9
15Teacher24441bosco_yip9
16tusharm25541UCSDKID8
17UCSDKID11344Apolloh6
18
Sheet1
 

apolloh

Board Regular
Joined
Jul 24, 2005
Messages
103
Hi,fairwinds

Thanks,your formula do work excellently.

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Domenic said:
SUBTOTAL({9,4,5},

Cool.

I've never though of even try that way. :biggrin:
 

apolloh

Board Regular
Joined
Jul 24, 2005
Messages
103
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

Board Regular
Joined
Jul 24, 2005
Messages
103
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top