| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extracts only Top of 5 records meet a value?
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to extracts only Top of 5 records meet a value?
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! In article , Son wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to extracts only Top of 5 records meet a value?
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! "Domenic" wrote: 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! In article , Son wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to extracts only Top of 5 records meet a value?
Assuming that A2:D119 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! In article , Son wrote: 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to extracts only Top of 5 records meet a value?
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. "Domenic" wrote: Assuming that A2:D119 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! In article , Son wrote: 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to extracts only Top of 5 records meet a value?
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 In article , Son wrote: 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. "Domenic" wrote: Assuming that A2:D119 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! In article , Son wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto-generating a list of records that meet a pattern criteria | Excel Discussion (Misc queries) | |||
| count records which meet filter criteria | Excel Discussion (Misc queries) | |||
| How do I show number of records that meet criteria filter | Excel Discussion (Misc queries) | |||
| Deleting specific records | Excel Discussion (Misc queries) | |||
| Number of records by Month that meet a specific requirement | Excel Worksheet Functions | |||
Linear Mode
