How to show last 5 games team's form

V

VKiprijan

New Member
Joined
Feb 24, 2021
Messages
40
Office Version
  1. 2016
Platform
  1. Windows
Hi Everyone,

I have a soccer league table with rankings in which I would like to show each team's form in the last 5 games played (Wins, Draw, Loss). What formula do I need to use in the columns CT thru CX in order to show this kind of statistics for each team?

Thank you all in advance.

Soccer League.xlsx
BPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCX
3German Bundes League
4
5Ranking SystemTotalHomeAwayLast 5 games
6RankingTeamPOINTSGDGSGAGPWDLGSGRGDPGPWDLGSGRGDPGPWDLGSGRGDP
79Eintracht Frankfurt90003411101051492431775330171326174572132-1117
81Bayern Munich100034198483344965171050461432351793437201730
97Wolfsburg7000341210954431146175733122922177362321224
1012Werder Bremen1200034105164960-11351751102527-216175462433-919
114Union Berlin400034168744321256179602591633177271923-423
1218Hertha Berlin180003467183762-2525175562326-3201712121436-225
1317Bochum161003484193369-3628176272230-8201722121139-288
148Mainz800034129105146545176643021924176362125-421
1510B. Monchengladbach1000034109124648-2391793431181330171681530-159
1614Hoffenheim140003495174252-1032176282427-320173391825-712
1713Augsburg130003497154055-1534176461621-522173392434-1012
185Freiburg410034168746388561794326131331177442025-525
192Dortmund20003420477340336417131148133540177362527-224
206Bayer Leverkusen60003414611544311481782633211226176452122-122
2116Stuttgart1600034610153954-1528175462120119171691834-169
223RB Leipzig30003417685537185717112232151735176462322122
2311FC Koln1100034911114249-738175552216620174662033-1318
2415Schalke150003479153159-2830175472134-1319172581025-1511
Bunde League
Cell Formulas
RangeFormula
BR7:BR24BR7=RANK(CC7,$CC$7:$CC$24)
BS7:BS24BS7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24BT7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24BU7=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24BV7=CD7+CL7
CD7:CD24CD7=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24CE7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24CF7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24CG7=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24CH7=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24CI7=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24CJ7=CH7-CI7
CK7:CK24,CS7:CS24CK7=CE7*3+CF7*1
CL7:CL24CL7=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24CM7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24CN7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($G$5:$G$346<>""))
CO7:CO24CO7=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24CP7=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24CQ7=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24BP7=SUM(BR7:BU7)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
what column between G and G have the date of the game played?
 
Upvote 0
and it would be great if you could post in a separate mini worksheet C1:G346, or a couple minis that we can splice together.
 
Upvote 0
awoohaw said:
and it would be great if you could post in a separate mini worksheet C1:G346, or a couple minis that we can splice together.
Click to expand...

Hello Awoohaw, here is the table with the games played:

Soccer League.xlsx
BCDEFGH
3Round
4DateHome1st Half2nd HalfAway
505.08.2022Eintracht Frankfurt0516Bayern Munich
606.08.2022Wolfsburg1222Werder Bremen
706.08.2022Union Berlin1031Hertha Berlin
806.08.2022Bochum1112Mainz
906.08.2022B. Monchengladbach1131Hoffenheim
1006.08.2022Augsburg0004Freiburg
1106.08.2022Dortmund1010Bayer Leverkusen
1207.08.2022Stuttgart1111RB Leipzig
1307.08.2022FC Koln0031Schalke
1412.08.2022Freiburg1013Dortmund
1513.08.2022Werder Bremen1122Stuttgart
1613.08.2022RB Leipzig1122FC Koln
1713.08.2022Hoffenheim2232Bochum
1813.08.2022Hertha Berlin1011Eintracht Frankfurt
1913.08.2022Bayer Leverkusen1112Augsburg
2013.08.2022Schalke1022B. Monchengladbach
2114.08.2022Mainz0000Union Berlin
2214.08.2022Bayern Munich2020Wolfsburg
2319.08.2022B. Monchengladbach1010Hertha Berlin
2420.08.2022Wolfsburg0000Schalke
2520.08.2022Stuttgart0101Freiburg
2620.08.2022Dortmund1023Werder Bremen
2720.08.2022Bayer Leverkusen0203Hoffenheim
2820.08.2022Augsburg1112Mainz
2920.08.2022Union Berlin2021RB Leipzig
3021.08.2022Eintracht Frankfurt0011FC Koln
3121.08.2022Bochum0407Bayern Munich
3226.08.2022Freiburg0010Bochum
3327.08.2022Schalke1316Union Berlin
3427.08.2022RB Leipzig1020Wolfsburg
3527.08.2022Mainz0303Bayer Leverkusen
3627.08.2022Hoffenheim1010Augsburg
3727.08.2022Hertha Berlin0101Dortmund
3827.08.2022Bayern Munich0111B. Monchengladbach
3928.08.2022FC Koln0000Stuttgart
4028.08.2022Werder Bremen2334Eintracht Frankfurt
4102.09.2022Dortmund1010Hoffenheim
4203.09.2022Wolfsburg1324FC Koln
4303.09.2022Union Berlin1111Bayern Munich
4403.09.2022Stuttgart1111Schalke
4503.09.2022Bochum0002Werder Bremen
4603.09.2022Bayer Leverkusen1023Freiburg
4703.09.2022Eintracht Frankfurt2040RB Leipzig
4804.09.2022Augsburg0002Hertha Berlin
4904.09.2022B. Monchengladbach0001Mainz
5009.09.2022Werder Bremen0001Augsburg
5110.09.2022RB Leipzig2030Dortmund
5210.09.2022Hoffenheim0041Mainz
5310.09.2022Hertha Berlin0022Bayer Leverkusen
5410.09.2022Eintracht Frankfurt0001Wolfsburg
5510.09.2022Bayern Munich1022Stuttgart
5610.09.2022Schalke1031Bochum
5711.09.2022FC Koln0101Union Berlin
5811.09.2022Freiburg0000B. Monchengladbach
5916.09.2022Mainz0111Hertha Berlin
6017.09.2022Stuttgart0113Eintracht Frankfurt
6117.09.2022Dortmund0010Schalke
6217.09.2022Bayer Leverkusen0011Werder Bremen
6317.09.2022Augsburg0010Bayern Munich
6417.09.2022B. Monchengladbach2030RB Leipzig
6518.09.2022Union Berlin0020Wolfsburg
6618.09.2022Bochum1011FC Koln
6718.09.2022Hoffenheim0000Freiburg
6830.09.2022Bayern Munich3040Bayer Leverkusen
6901.10.2022Wolfsburg2232Stuttgart
7001.10.2022RB Leipzig2040Bochum
7101.10.2022Freiburg2021Mainz
7201.10.2022FC Koln0132Dortmund
7301.10.2022Eintracht Frankfurt2020Union Berlin
7401.10.2022Werder Bremen4051B. Monchengladbach
7502.10.2022Hertha Berlin1111Hoffenheim
7602.10.2022Schalke1223Augsburg
7707.10.2022Hoffenheim1112Werder Bremen
7808.10.2022Mainz1011RB Leipzig
7908.10.2022Bochum0030Eintracht Frankfurt
8008.10.2022Bayer Leverkusen2040Schalke
8108.10.2022Augsburg0111Wolfsburg
8208.10.2022Dortmund0122Bayern Munich
8309.10.2022B. Monchengladbach2152FC Koln
8409.10.2022Hertha Berlin1122Freiburg
8509.10.2022Stuttgart0001Union Berlin
8614.10.2022Schalke0203Hoffenheim
8715.10.2022Wolfsburg1122B. Monchengladbach
8815.10.2022Werder Bremen0102Mainz
8915.10.2022Stuttgart2141Bochum
9015.10.2022Eintracht Frankfurt1051Bayer Leverkusen
9115.10.2022RB Leipzig3032Hertha Berlin
9216.10.2022FC Koln0132Augsburg
9316.10.2022Union Berlin2020Dortmund
9416.10.2022Bayern Munich2050Freiburg
9521.10.2022Mainz3050FC Koln
9622.10.2022Hoffenheim0202Bayern Munich
9722.10.2022Freiburg0020Werder Bremen
9822.10.2022Dortmund3050Stuttgart
9922.10.2022Bayer Leverkusen1122Wolfsburg
10022.10.2022Augsburg1033RB Leipzig
10122.10.2022B. Monchengladbach0313Eintracht Frankfurt
10223.10.2022Bochum1021Union Berlin
10323.10.2022Hertha Berlin0021Schalke
10428.10.2022Werder Bremen0010Hertha Berlin
10529.10.2022Wolfsburg2040Bochum
10629.10.2022Stuttgart1121Augsburg
10729.10.2022RB Leipzig1020Bayer Leverkusen
10829.10.2022Bayern Munich3162Mainz
10929.10.2022Eintracht Frankfurt1112Dortmund
11030.10.2022Union Berlin0121B. Monchengladbach
11130.10.2022Schalke0102Freiburg
11230.10.2022FC Koln1111Hoffenheim
11304.11.2022B. Monchengladbach2131Stuttgart
11405.11.2022Mainz0103Wolfsburg
11505.11.2022Hoffenheim0113RB Leipzig
11605.11.2022Hertha Berlin2323Bayern Munich
11705.11.2022Dortmund3030Bochum
11805.11.2022Augsburg1112Eintracht Frankfurt
11905.11.2022Werder Bremen1021Schalke
12006.11.2022Bayer Leverkusen0050Union Berlin
12106.11.2022Freiburg0020FC Koln
12208.11.2022Wolfsburg1020Dortmund
12308.11.2022Stuttgart1121Hertha Berlin
12408.11.2022Bochum2021B. Monchengladbach
12508.11.2022Bayern Munich4161Werder Bremen
12609.11.2022FC Koln1012Bayer Leverkusen
12709.11.2022Union Berlin2222Augsburg
12809.11.2022Schalke1010Mainz
12909.11.2022RB Leipzig0031Freiburg
13009.11.2022Eintracht Frankfurt3142Hoffenheim
13111.11.2022B. Monchengladbach3242Dortmund
13212.11.2022Werder Bremen0112RB Leipzig
13312.11.2022Hoffenheim1112Wolfsburg
13412.11.2022Hertha Berlin1020FC Koln
13512.11.2022Bayer Leverkusen1020Stuttgart
13612.11.2022Augsburg0001Bochum
13712.11.2022Schalke0102Bayern Munich
13813.11.2022Mainz1011Eintracht Frankfurt
13913.11.2022Freiburg4041Union Berlin
14020.01.2023RB Leipzig0111Bayern Munich
14121.01.2023Wolfsburg3060Freiburg
14221.01.2023Union Berlin0131Hoffenheim
14321.01.2023Stuttgart1111Mainz
14421.01.2023Eintracht Frankfurt1030Schalke
14521.01.2023Bochum2031Hertha Berlin
14621.01.2023FC Koln5171Werder Bremen
14722.01.2023Dortmund2243Augsburg
14822.01.2023B. Monchengladbach0223Bayer Leverkusen
14924.01.2023Schalke0416RB Leipzig
15024.01.2023Hoffenheim1122Stuttgart
15124.01.2023Hertha Berlin0305Wolfsburg
15224.01.2023Bayern Munich0111FC Koln
15325.01.2023Mainz1112Dortmund
15425.01.2023Werder Bremen1112Union Berlin
15525.01.2023Freiburg0111Eintracht Frankfurt
15625.01.2023Bayer Leverkusen1020Bochum
15725.01.2023Augsburg0010B. Monchengladbach
15827.01.2023RB Leipzig1021Stuttgart
15928.01.2023Werder Bremen1021Wolfsburg
16028.01.2023Mainz3052Bochum
16128.01.2023Hoffenheim0214B. Monchengladbach
16228.01.2023Hertha Berlin0102Union Berlin
16328.01.2023Freiburg2131Augsburg
16428.01.2023Bayern Munich1011Eintracht Frankfurt
16529.01.2023Schalke0000FC Koln
16629.01.2023Bayer Leverkusen0102Dortmund
Bunde League
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H5:H13Cell ValueduplicatestextNO
C5:C13Cell ValueduplicatestextNO
 
Upvote 0
here is the second part of the table with the games played and the remaining ones to be played:

Soccer League.xlsx
BCDEFGH
16703.02.2023Augsburg0010Bayer Leverkusen
16804.02.2023Union Berlin1021Mainz
16904.02.2023FC Koln0000RB Leipzig
17004.02.2023Eintracht Frankfurt2030Hertha Berlin
17104.02.2023Dortmund1151Freiburg
17204.02.2023Bochum3052Hoffenheim
17304.02.2023B. Monchengladbach0000Schalke
17405.02.2023Stuttgart0002Werder Bremen
17505.02.2023Wolfsburg1324Bayern Munich
17610.02.2023Schalke0000Wolfsburg
17711.02.2023Werder Bremen0002Dortmund
17811.02.2023Mainz2131Augsburg
17911.02.2023Hoffenheim0113Bayer Leverkusen
18011.02.2023Freiburg0121Stuttgart
18111.02.2023Bayern Munich1030Bochum
18211.02.2023RB Leipzig1012Union Berlin
18312.02.2023Hertha Berlin1141B. Monchengladbach
18412.02.2023FC Koln0030Eintracht Frankfurt
18517.02.2023Augsburg0010Hoffenheim
18618.02.2023Wolfsburg0103RB Leipzig
18718.02.2023Stuttgart1030FC Koln
18818.02.2023Bochum0102Freiburg
18918.02.2023B. Monchengladbach1132Bayern Munich
19018.02.2023Eintracht Frankfurt1020Werder Bremen
19119.02.2023Union Berlin0000Schalke
19219.02.2023Dortmund2041Hertha Berlin
19319.02.2023Bayer Leverkusen1223Mainz
19424.02.2023Mainz1040B. Monchengladbach
19525.02.2023Werder Bremen2030Bochum
19625.02.2023RB Leipzig2021Eintracht Frankfurt
19725.02.2023Hoffenheim0101Dortmund
19825.02.2023Hertha Berlin0020Augsburg
19925.02.2023FC Koln0102Wolfsburg
20025.02.2023Schalke2021Stuttgart
20126.02.2023Freiburg1011Bayer Leverkusen
20226.02.2023Bayern Munich3030Union Berlin
20303.03.2023Dortmund2021RB Leipzig
20404.03.2023Union Berlin0000FC Koln
20504.03.2023Mainz1010Hoffenheim
20604.03.2023Bochum0102Schalke
20704.03.2023B. Monchengladbach0000Freiburg
20804.03.2023Augsburg1121Werder Bremen
20904.03.2023Stuttgart0112Bayern Munich
21005.03.2023Bayer Leverkusen2041Hertha Berlin
21105.03.2023Wolfsburg2222Eintracht Frankfurt
21210.03.2023FC Koln0102Bochum
21311.03.2023RB Leipzig0030B. Monchengladbach
21411.03.2023Hertha Berlin1011Mainz
21511.03.2023Eintracht Frankfurt0011Stuttgart
21611.03.2023Bayern Munich4153Augsburg
21711.03.2023Schalke0122Dortmund
21812.03.2023Freiburg1021Hoffenheim
21912.03.2023Werder Bremen1123Bayer Leverkusen
22012.03.2023Wolfsburg0011Union Berlin
22117.03.2023B. Monchengladbach0022Werder Bremen
22218.03.2023Stuttgart0001Wolfsburg
22318.03.2023Hoffenheim2031Hertha Berlin
22418.03.2023Bochum0010RB Leipzig
22518.03.2023Augsburg0011Schalke
22618.03.2023Dortmund4161FC Koln
22719.03.2023Union Berlin0020Eintracht Frankfurt
22819.03.2023Bayer Leverkusen0121Bayern Munich
22919.03.2023Mainz0011Freiburg
23031.03.2023Eintracht Frankfurt1111Bochum
23101.04.2023Wolfsburg0222Augsburg
23201.04.2023Union Berlin0030Stuttgart
23301.04.2023Schalke0003Bayer Leverkusen
23401.04.2023RB Leipzig0103Mainz
23501.04.2023Freiburg0011Hertha Berlin
23601.04.2023Bayern Munich3042Dortmund
23702.04.2023FC Koln0000B. Monchengladbach
23802.04.2023Werder Bremen0012Hoffenheim
23908.04.2023Mainz0022Werder Bremen
24008.04.2023Freiburg0001Bayern Munich
24108.04.2023Dortmund1021Union Berlin
24208.04.2023Bayer Leverkusen2031Eintracht Frankfurt
24308.04.2023Augsburg1213FC Koln
24408.04.2023Hertha Berlin0101RB Leipzig
24509.04.2023B. Monchengladbach1020Wolfsburg
24609.04.2023Bochum0123Stuttgart
24709.04.2023Hoffenheim1020Schalke
24814.04.2023Schalke2152Hertha Berlin
24915.04.2023Stuttgart0233Dortmund
25015.04.2023RB Leipzig3132Augsburg
25115.04.2023FC Koln0111Mainz
25215.04.2023Bayern Munich1011Hoffenheim
25315.04.2023Eintracht Frankfurt0111B. Monchengladbach
25416.04.2023Werder Bremen0012Freiburg
25516.04.2023Union Berlin1011Bochum
25616.04.2023Wolfsburg0000Bayer Leverkusen
25721.04.2023Augsburg1011Stuttgart
25822.04.2023Mainz0131Bayern Munich
25922.04.2023Hoffenheim0213FC Koln
26022.04.2023Hertha Berlin0224Werder Bremen
26122.04.2023Bochum0315Wolfsburg
26222.04.2023Dortmund3040Eintracht Frankfurt
26323.04.2023Freiburg2040Schalke
26423.04.2023Bayer Leverkusen1020RB Leipzig
26523.04.2023B. Monchengladbach0001Union Berlin
26628.04.2023Bochum1111Dortmund
26729.04.2023Union Berlin0000Bayer Leverkusen
26829.04.2023Stuttgart1021B. Monchengladbach
26929.04.2023RB Leipzig1010Hoffenheim
27029.04.2023FC Koln0001Freiburg
27129.04.2023Eintracht Frankfurt1011Augsburg
27229.04.2023Schalke0121Werder Bremen
27330.04.2023Bayern Munich0020Hertha Berlin
27430.04.2023Wolfsburg3030Mainz
27505.05.2023Bayer Leverkusen1212FC Koln
27605.05.2023Mainz0123Schalke
27706.05.2023Augsburg0010Union Berlin
27806.05.2023B. Monchengladbach1020Bochum
27906.05.2023Freiburg0001RB Leipzig
28006.05.2023Hertha Berlin2121Stuttgart
28106.05.2023Hoffenheim3031Eintracht Frankfurt
28206.05.2023Werder Bremen0012Bayern Munich
28307.05.2023Dortmund3060Wolfsburg
284FC KolnHertha Berlin
285Bayern MunichSchalke
286BochumAugsburg
287Eintracht FrankfurtMainz
288Union BerlinFreiburg
289WolfsburgHoffenheim
290DortmundB. Monchengladbach
291StuttgartBayer Leverkusen
292RB LeipzigWerder Bremen
293FreiburgWolfsburg
294Hertha BerlinBochum
295HoffenheimUnion Berlin
296SchalkeEintracht Frankfurt
297Werder BremenFC Koln
298Bayern MunichRB Leipzig
299MainzStuttgart
300AugsburgDortmund
301Bayer LeverkusenB. Monchengladbach
302B. MonchengladbachAugsburg
303BochumBayer Leverkusen
304DortmundMainz
305Eintracht FrankfurtFreiburg
306FC KolnBayern Munich
307RB LeipzigSchalke
308StuttgartHoffenheim
309Union BerlinWerder Bremen
310WolfsburgHertha Berlin
Bunde League
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H194:H202Cell ValueduplicatestextNO
C194:C202Cell ValueduplicatestextNO
 
Upvote 0
Okay, try this:
Mr excel questions 35.xlsm
BPBQCTCUCV
1German Bundes League
2
3Last 5 games
4RankingTeamWLD
59Eintracht Frankfurt032
61Bayern Munich311
77Wolfsburg221
812Werder Bremen131
94Union Berlin122
1018Hertha Berlin140
1117Bochum032
128Mainz122
1310B. Monchengladbach221
1414Hoffenheim221
1513Augsburg122
165Freiburg320
172Dortmund302
186Bayer Leverkusen212
1916Stuttgart212
203RB Leipzig410
2111FC Koln311
2215Schalke320
Sheet3
Cell Formulas
RangeFormula
CT5:CT22CT5=SUM((--(AGGREGATE(14,6,((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282),5) <=(((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282)) *( ((--($BQ5=$C$4:$C$282))*($F$4:$F$282>$G$4:$G$282)) +((--($BQ5=$H$4:$H$282))*($G$4:$G$282>$F$4:$F$282)) ))))
CU5:CU22CU5=SUM((--(AGGREGATE(14,6,((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282),5) <=(((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282)) *( ((--($BQ5=$C$4:$C$282))*($F$4:$F$282<$G$4:$G$282)) +((--($BQ5=$H$4:$H$282))*($G$4:$G$282<$F$4:$F$282)) ))))
CV5:CV22CV5=SUM((--(AGGREGATE(14,6,((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282),5) <=(((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282)) *( ((--($BQ5=$C$4:$C$282))*($F$4:$F$282=$G$4:$G$282)) +((--($BQ5=$H$4:$H$282))*($G$4:$G$282=$F$4:$F$282)) ))))
BP5:BP22BP5=SUM(BR5:BU5)
 
Upvote 0
How about
Fluff.xlsm
BPBQCTCUCVCWCX
5Last 5 games
6RankingTeam
79Eintracht FrankfurtLDLDL
81Bayern MunichWLLDW
97WolfsburgLLWDL
1012Werder BremenLLWWD
114Union BerlinLDWDL
1218Hertha BerlinWLWLW
1317BochumLDWDW
148MainzLLLDD
1510B. MonchengladbachWLWDL
1614HoffenheimWLWDL
1713AugsburgWDDLW
185FreiburgLWLWW
192DortmundWDLDL
206Bayer LeverkusenLDLDL
2116StuttgartLLDDW
223RB LeipzigWLLLW
2311FC KolnWWWDW
2415SchalkeWLLLL
Master
Cell Formulas
RangeFormula
CT7:CX24CT7=CHOOSE(SIGN(INDEX($F:$F,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))-INDEX($G:$G,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7))))*(IF(INDEX($C:$C,AGGREGATE(14,6,ROW($C$5:$C$500)/(($C$5:$C$500=$BQ7)+($H$5:$H$500=$BQ7))/($F$5:$F$500<>""),COLUMNS($CT$7:CT$7)))=BQ7,1,-1))+2,"L","D","W")
BP7:BP24BP7=SUM(BR7:BU7)
 
Upvote 0
awoohaw said:
Okay, try this:
Mr excel questions 35.xlsm
BPBQCTCUCV
1German Bundes League
2
3Last 5 games
4RankingTeamWLD
59Eintracht Frankfurt032
61Bayern Munich311
77Wolfsburg221
812Werder Bremen131
94Union Berlin122
1018Hertha Berlin140
1117Bochum032
128Mainz122
1310B. Monchengladbach221
1414Hoffenheim221
1513Augsburg122
165Freiburg320
172Dortmund302
186Bayer Leverkusen212
1916Stuttgart212
203RB Leipzig410
2111FC Koln311
2215Schalke320
Sheet3
Cell Formulas
RangeFormula
CT5:CT22CT5=SUM((--(AGGREGATE(14,6,((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282),5) <=(((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282)) *( ((--($BQ5=$C$4:$C$282))*($F$4:$F$282>$G$4:$G$282)) +((--($BQ5=$H$4:$H$282))*($G$4:$G$282>$F$4:$F$282)) ))))
CU5:CU22CU5=SUM((--(AGGREGATE(14,6,((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282),5) <=(((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282)) *( ((--($BQ5=$C$4:$C$282))*($F$4:$F$282<$G$4:$G$282)) +((--($BQ5=$H$4:$H$282))*($G$4:$G$282<$F$4:$F$282)) ))))
CV5:CV22CV5=SUM((--(AGGREGATE(14,6,((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282),5) <=(((--($BQ5=$C$4:$C$282))+(--($BQ5=$H$4:$H$282)))*($B$4:$B$282)) *( ((--($BQ5=$C$4:$C$282))*($F$4:$F$282=$G$4:$G$282)) +((--($BQ5=$H$4:$H$282))*($G$4:$G$282=$F$4:$F$282)) ))))
BP5:BP22BP5=SUM(BR5:BU5)
Click to expand...

Unfortunately, this formula doesn't works for some reason :(
 
Upvote 0
What about the formula in post#7?
 
Upvote 0
VKiprijan said:
Unfortunately, this formula doesn't works for some reason :(
Click to expand...
Not really sure why you wrote that, as you can see I am using your cell references. Note that i have hidden your large table.
Can you post your worksheet as a mini workbook using xl2bb so we can see the formulas you have?
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
IF and AND function with COUNTIF to calculate only populated cells
Replies
2
Views
490
VKiprijan
V
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
518
Rnkhch
R
F
  • Solved
Formula - Sumif(s)? Sumproduct? etc.
2
Replies
16
Views
278
Fluff
Fluff
B
  • Solved
SUMPRODUCT with FILTER Criteria
Replies
3
Views
333
Fluff
Fluff
J
  • Solved
conditional formatting based cells if there are duplicate cells in a range
Replies
9
Views
128
StephenCrump
S

Forum statistics

Threads
1,223,885
Messages
6,175,181
Members
452,615
Latest member
bogeys2birdies

Share this page

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
Back
Top