S
Son
I have a list of my football teams, but I want to calculate the number of
goal of the top last 5 teams. Please help me. Thank you.
goal of the top last 5 teams. Please help me. Thank you.
D
Domenic
The following will provide you with a Top 5 list, and will take ties
into consideration...
Assumptions:
A2:A10 contains the team name
B2:B10 contains the corresponding goals
Formula:
D2: 5
This indicates that you want a Top 5 list. If, for example, you want a
Top 10 list, enter 10 instead, and so on.
E2:
=COUNTIF(B2:B10,">="&LARGE(B2:B10,D2))
F2:
=IF(ROWS(F$2:F2)<=$E$2,LARGE(B$2:B$10,ROWS(F$2:F2)),"")
G2, copied down:
=IF($F2<>"",INDEX(A$2:A$10,SMALL(IF($B$2:$B$10=$F2,ROW($B$2:$B$10)-ROW($B
$2)+1),COUNTIF($F$2:$F2,$F2))),"")
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
into consideration...
Assumptions:
A2:A10 contains the team name
B2:B10 contains the corresponding goals
Formula:
D2: 5
This indicates that you want a Top 5 list. If, for example, you want a
Top 10 list, enter 10 instead, and so on.
E2:
=COUNTIF(B2:B10,">="&LARGE(B2:B10,D2))
F2:
=IF(ROWS(F$2:F2)<=$E$2,LARGE(B$2:B$10,ROWS(F$2:F2)),"")
G2, copied down:
=IF($F2<>"",INDEX(A$2:A$10,SMALL(IF($B$2:$B$10=$F2,ROW($B$2:$B$10)-ROW($B
$2)+1),COUNTIF($F$2:$F2,$F2))),"")
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
S
Son
Thank for your help!
But it is not the exactly result.
For example
I have List like that
Team1 Goal Team 2
Bayern Munich 2 0 B.Dortmund
Mainz 05 FSV 2 1 Bochum
Hamburg 1 1 A.Bielefeld
Schalke 1 1 E.Frankfurt
Stuttgart 0 3 Nurnberg
B.M'gladbach 2 0 Cottbus
B. Leverkusen 3 0 TSV Alemannia Aachen
Hanover 2 4 W.Bremen
Wolfsburg 0 0 Hertha Berlin
Nurnberg 1 0 B.M'gladbach
E.Frankfurt 0 0 Wolfsburg
TSV Alemannia Aachen 0 1 Schalke
W.Bremen 2 1 B. Leverkusen
Cottbus 2 2 Hamburg
B.Dortmund 1 1 Mainz 05 FSV
Hertha Berlin 4 0 Hanover
Bochum 1 2 Bayern Munich
A.Bielefeld 2 3 Stuttgart
Schalke 2 0 W.Bremen
Stuttgart 1 3 B.Dortmund
Bayern Munich 0 0 Nurnberg
B.M'gladbach 1 0 A.Bielefeld
B. Leverkusen 1 1 Wolfsburg
Hanover 0 3 TSV Alemannia Aachen
Bochum 0 1 Cottbus
Hamburg 1 1 Hertha Berlin
Mainz 05 FSV 1 1 E.Frankfurt
Wolfsburg 1 2 Hanover
B.Dortmund 1 0 Hamburg
TSV Alemannia Aachen 4 2 B.M'gladbach
A.Bielefeld 2 1 Bayern Munich
W.Bremen 2 3 Stuttgart
Nurnberg 1 1 Bochum
Cottbus 2 0 Mainz 05 FSV
E.Frankfurt 3 1 B. Leverkusen
Hertha Berlin 2 0 Schalke
B.M'gladbach 1 0 B.Dortmund
Mainz 05 FSV 1 1 Hertha Berlin
Schalke 2 0 Wolfsburg
Hanover 1 1 B. Leverkusen
Stuttgart 1 1 E.Frankfurt
Hamburg 1 1 W.Bremen
Bayern Munich 2 1 TSV Alemannia Aachen
Bochum 2 1 A.Bielefeld
Cottbus 1 1 Nurnberg
B.Dortmund 2 2 Hanover
E.Frankfurt 2 2 Hamburg
TSV Alemannia Aachen 2 1 Bochum
W.Bremen 3 0 B.M'gladbach
Wolfsburg 1 0 Bayern Munich
Nurnberg 1 1 Mainz 05 FSV
A.Bielefeld 3 1 Cottbus
B. Leverkusen 3 1 Schalke
Hertha Berlin 2 2 Stuttgart
Mainz 05 FSV 1 3 TSV Alemannia Aachen
Stuttgart 3 0 B. Leverkusen
Hanover 1 1 E.Frankfurt
Bochum 0 6 W.Bremen
Hamburg 1 2 Schalke
B.M'gladbach 3 1 Wolfsburg
Bayern Munich 4 2 Hertha Berlin
Nurnberg 1 1 A.Bielefeld
Cottbus 2 3 B.Dortmund
B.Dortmund 1 1 Bochum
Wolfsburg 1 1 Stuttgart
Hertha Berlin 2 1 B.M'gladbach
W.Bremen 3 1 Bayern Munich
TSV Alemannia Aachen 1 2 Cottbus
A.Bielefeld 1 0 Mainz 05 FSV
Schalke 2 1 Hanover
B. Leverkusen 1 2 Hamburg
E.Frankfurt 2 2 Nurnberg
Mainz 05 FSV 1 6 W.Bremen
Hamburg 0 0 Hanover
Bochum 0 1 Wolfsburg
Cottbus 2 0 Hertha Berlin
B.M'gladbach 0 2 B. Leverkusen
Nurnberg 1 1 B.Dortmund
Bayern Munich 2 0 E.Frankfurt
Stuttgart 3 0 Schalke
A.Bielefeld 5 1 TSV Alemannia Aachen
Hanover 0 2 Bochum
Wolfsburg 0 0 Hamburg
B.Dortmund 1 1 A.Bielefeld
TSV Alemannia Aachen 2 4 Stuttgart
Hertha Berlin 2 1 Nurnberg
W.Bremen 1 1 Cottbus
B. Leverkusen 1 1 Mainz 05 FSV
E.Frankfurt 1 0 B.M'gladbach
Schalke 2 2 Bayern Munich
Nurnberg 1 2 W.Bremen
Stuttgart 2 0 Hamburg
Mainz 05 FSV 1 2 Wolfsburg
B.Dortmund 0 0 TSV Alemannia Aachen
Bochum 1 3 B. Leverkusen
Cottbus 0 1 E.Frankfurt
B.M'gladbach 0 2 Schalke
A.Bielefeld 2 2 Hertha Berlin
Bayern Munich 0 1 Hanover
W.Bremen 1 3 B.Dortmund
Wolfsburg 0 0 Cottbus
E.Frankfurt 0 3 A.Bielefeld
Hertha Berlin 3 3 Bochum
B. Leverkusen 2 3 Bayern Munich
Schalke 4 0 Mainz 05 FSV
Hamburg 1 1 B.M'gladbach
TSV Alemannia Aachen 1 1 Nurnberg
Hanover 1 2 Stuttgart
TSV Alemannia Aachen 1 1 Nurnberg
Bochum 4 3 E.Frankfurt
B.Dortmund 1 2 Hertha Berlin
Mainz 05 FSV 0 0 Hamburg
Nurnberg 3 2 B. Leverkusen
TSV Alemannia Aachen 2 2 W.Bremen
Bayern Munich 2 1 Stuttgart
Cottbus 2 4 Schalke
A.Bielefeld 0 0 Wolfsburg
B.M'gladbach 0 1 Hanover
After Filter Team 1, we have
Cottbus 2 2 Hamburg 19-08-06
Cottbus 2 0 Mainz 05 FSV 16-09-06
Cottbus 1 1 Nurnberg 24-09-06
Cottbus 2 3 B.Dortmund 15-10-06
Cottbus 2 0 Hertha Berlin 28-10-06
Cottbus 0 1 E.Frankfurt 08-11-06
Cottbus 2 4 Schalke 18-11-06
There are seven record for Cottbus, but I only want the last 5 record (not
7).
The object is how many goal Cottbus have in the last 5 record.
So Please help me the function from the beginning to get (not use filter).
The same is to the team 2.
Thank you!
But it is not the exactly result.
For example
I have List like that
Team1 Goal Team 2
Bayern Munich 2 0 B.Dortmund
Mainz 05 FSV 2 1 Bochum
Hamburg 1 1 A.Bielefeld
Schalke 1 1 E.Frankfurt
Stuttgart 0 3 Nurnberg
B.M'gladbach 2 0 Cottbus
B. Leverkusen 3 0 TSV Alemannia Aachen
Hanover 2 4 W.Bremen
Wolfsburg 0 0 Hertha Berlin
Nurnberg 1 0 B.M'gladbach
E.Frankfurt 0 0 Wolfsburg
TSV Alemannia Aachen 0 1 Schalke
W.Bremen 2 1 B. Leverkusen
Cottbus 2 2 Hamburg
B.Dortmund 1 1 Mainz 05 FSV
Hertha Berlin 4 0 Hanover
Bochum 1 2 Bayern Munich
A.Bielefeld 2 3 Stuttgart
Schalke 2 0 W.Bremen
Stuttgart 1 3 B.Dortmund
Bayern Munich 0 0 Nurnberg
B.M'gladbach 1 0 A.Bielefeld
B. Leverkusen 1 1 Wolfsburg
Hanover 0 3 TSV Alemannia Aachen
Bochum 0 1 Cottbus
Hamburg 1 1 Hertha Berlin
Mainz 05 FSV 1 1 E.Frankfurt
Wolfsburg 1 2 Hanover
B.Dortmund 1 0 Hamburg
TSV Alemannia Aachen 4 2 B.M'gladbach
A.Bielefeld 2 1 Bayern Munich
W.Bremen 2 3 Stuttgart
Nurnberg 1 1 Bochum
Cottbus 2 0 Mainz 05 FSV
E.Frankfurt 3 1 B. Leverkusen
Hertha Berlin 2 0 Schalke
B.M'gladbach 1 0 B.Dortmund
Mainz 05 FSV 1 1 Hertha Berlin
Schalke 2 0 Wolfsburg
Hanover 1 1 B. Leverkusen
Stuttgart 1 1 E.Frankfurt
Hamburg 1 1 W.Bremen
Bayern Munich 2 1 TSV Alemannia Aachen
Bochum 2 1 A.Bielefeld
Cottbus 1 1 Nurnberg
B.Dortmund 2 2 Hanover
E.Frankfurt 2 2 Hamburg
TSV Alemannia Aachen 2 1 Bochum
W.Bremen 3 0 B.M'gladbach
Wolfsburg 1 0 Bayern Munich
Nurnberg 1 1 Mainz 05 FSV
A.Bielefeld 3 1 Cottbus
B. Leverkusen 3 1 Schalke
Hertha Berlin 2 2 Stuttgart
Mainz 05 FSV 1 3 TSV Alemannia Aachen
Stuttgart 3 0 B. Leverkusen
Hanover 1 1 E.Frankfurt
Bochum 0 6 W.Bremen
Hamburg 1 2 Schalke
B.M'gladbach 3 1 Wolfsburg
Bayern Munich 4 2 Hertha Berlin
Nurnberg 1 1 A.Bielefeld
Cottbus 2 3 B.Dortmund
B.Dortmund 1 1 Bochum
Wolfsburg 1 1 Stuttgart
Hertha Berlin 2 1 B.M'gladbach
W.Bremen 3 1 Bayern Munich
TSV Alemannia Aachen 1 2 Cottbus
A.Bielefeld 1 0 Mainz 05 FSV
Schalke 2 1 Hanover
B. Leverkusen 1 2 Hamburg
E.Frankfurt 2 2 Nurnberg
Mainz 05 FSV 1 6 W.Bremen
Hamburg 0 0 Hanover
Bochum 0 1 Wolfsburg
Cottbus 2 0 Hertha Berlin
B.M'gladbach 0 2 B. Leverkusen
Nurnberg 1 1 B.Dortmund
Bayern Munich 2 0 E.Frankfurt
Stuttgart 3 0 Schalke
A.Bielefeld 5 1 TSV Alemannia Aachen
Hanover 0 2 Bochum
Wolfsburg 0 0 Hamburg
B.Dortmund 1 1 A.Bielefeld
TSV Alemannia Aachen 2 4 Stuttgart
Hertha Berlin 2 1 Nurnberg
W.Bremen 1 1 Cottbus
B. Leverkusen 1 1 Mainz 05 FSV
E.Frankfurt 1 0 B.M'gladbach
Schalke 2 2 Bayern Munich
Nurnberg 1 2 W.Bremen
Stuttgart 2 0 Hamburg
Mainz 05 FSV 1 2 Wolfsburg
B.Dortmund 0 0 TSV Alemannia Aachen
Bochum 1 3 B. Leverkusen
Cottbus 0 1 E.Frankfurt
B.M'gladbach 0 2 Schalke
A.Bielefeld 2 2 Hertha Berlin
Bayern Munich 0 1 Hanover
W.Bremen 1 3 B.Dortmund
Wolfsburg 0 0 Cottbus
E.Frankfurt 0 3 A.Bielefeld
Hertha Berlin 3 3 Bochum
B. Leverkusen 2 3 Bayern Munich
Schalke 4 0 Mainz 05 FSV
Hamburg 1 1 B.M'gladbach
TSV Alemannia Aachen 1 1 Nurnberg
Hanover 1 2 Stuttgart
TSV Alemannia Aachen 1 1 Nurnberg
Bochum 4 3 E.Frankfurt
B.Dortmund 1 2 Hertha Berlin
Mainz 05 FSV 0 0 Hamburg
Nurnberg 3 2 B. Leverkusen
TSV Alemannia Aachen 2 2 W.Bremen
Bayern Munich 2 1 Stuttgart
Cottbus 2 4 Schalke
A.Bielefeld 0 0 Wolfsburg
B.M'gladbach 0 1 Hanover
After Filter Team 1, we have
Cottbus 2 2 Hamburg 19-08-06
Cottbus 2 0 Mainz 05 FSV 16-09-06
Cottbus 1 1 Nurnberg 24-09-06
Cottbus 2 3 B.Dortmund 15-10-06
Cottbus 2 0 Hertha Berlin 28-10-06
Cottbus 0 1 E.Frankfurt 08-11-06
Cottbus 2 4 Schalke 18-11-06
There are seven record for Cottbus, but I only want the last 5 record (not
7).
The object is how many goal Cottbus have in the last 5 record.
So Please help me the function from the beginning to get (not use filter).
The same is to the team 2.
Thank you!
D
Domenic
Assuming that A2
119 contains the data, try the following...
F2: enter the team of interest, such as Cottbus
G1: enter 5, indicating that you want the last 5 records
H1:
=MIN(G1,COUNTIF(A2:A119,F2))
G2, copied down and across:
=IF(ROWS(G$2:G2)<=$H$1,INDEX(A$2:A$119,LARGE(IF($A$2:$A$119=$F$2,ROW(A$2:
A$119)-ROW(A$2)+1),ROWS(G$2:G2))),"")
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
119 contains the data, try the following...F2: enter the team of interest, such as Cottbus
G1: enter 5, indicating that you want the last 5 records
H1:
=MIN(G1,COUNTIF(A2:A119,F2))
G2, copied down and across:
=IF(ROWS(G$2:G2)<=$H$1,INDEX(A$2:A$119,LARGE(IF($A$2:$A$119=$F$2,ROW(A$2:
A$119)-ROW(A$2)+1),ROWS(G$2:G2))),"")
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
S
Son
Thank you very much but
=IF(ROWS(G$2:G2)<=$H$1,INDEX(A$2:A$119,LARGE(IF($A$2:$A$119=$F$2,ROW(A$2:
A$119)-ROW(A$2)+1),ROWS(G$2:G2))),"")
It is does not works, it present in excell without the result after I
following your post. Please help me to fix that. Thank you.
I wonder if you don't mind to tell me how to think that formular.
Thank you.
=IF(ROWS(G$2:G2)<=$H$1,INDEX(A$2:A$119,LARGE(IF($A$2:$A$119=$F$2,ROW(A$2:
A$119)-ROW(A$2)+1),ROWS(G$2:G2))),"")
It is does not works, it present in excell without the result after I
following your post. Please help me to fix that. Thank you.
I wonder if you don't mind to tell me how to think that formular.
Thank you.
D
Domenic
Does it return an error or an incorrect result? Note that the formula
for G2 needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
In other words, type the formula, but instead of pressing just ENTER,
press both the CONTROL and SHIFT keys down, then while both keys are
pressed down, press the ENTER key. Excel will automatically place
braces {..} around the formula, indicating that you've entered the
formula correctly. Based on the example you provided, you should get
the following results...
Cottbus 2 4 Schalke
Cottbus 0 1 E.Frankfurt
Cottbus 2 0 Hertha Berlin
Cottbus 2 3 B.Dortmund
Cottbus 1 1 Nurnberg
for G2 needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
In other words, type the formula, but instead of pressing just ENTER,
press both the CONTROL and SHIFT keys down, then while both keys are
pressed down, press the ENTER key. Excel will automatically place
braces {..} around the formula, indicating that you've entered the
formula correctly. Based on the example you provided, you should get
the following results...
Cottbus 2 4 Schalke
Cottbus 0 1 E.Frankfurt
Cottbus 2 0 Hertha Berlin
Cottbus 2 3 B.Dortmund
Cottbus 1 1 Nurnberg