How to show last 5 games team's form

V

VKiprijan

New Member
Joined
Feb 24, 2021
Messages
29
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)
 
Sort by date Sort by votes
Fluff said:
See post#28, I already realised the error.
Click to expand...

Yes, now the formula for the away games works properly.

Is there a way with a formula or with a conditional formatting to highlight the Wins in green, the Losses in red and the Draws in yellow these last 5 games?

Thanks and apreciate it.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
VKiprijan said:
I believe that there was some problem with the formula in my Excel 2016 environment thus not working properly as Peter_SS pointed out.
Click to expand...
Did you try the suggestion that I made in post #16 which does use functions available in Excel 2016?

In relation to the Home/Away last 5, still trying to keep the formulas simpler by using a helper column, and including the green/red/yellow for W/L/D you could try this.

VKiprijan.xlsm
BCDEFGHIBPBQCTCUCVCWCXCYCZDADBDCDD
3RoundGerman Bundes League
4DateHome1st Half2nd HalfAway
505.08.2022Eintracht Frankfurt0516Bayern MunichLWHomeAway
606.08.2022Wolfsburg1222Werder BremenDDRankingTeam
706.08.2022Union Berlin1031Hertha BerlinWL9Eintracht FrankfurtDDDDWLLLLD
806.08.2022Bochum1112MainzLW1Bayern MunichWDWWWWLWLW
906.08.2022B. Monchengladbach1131HoffenheimWL7WolfsburgWDDDDLWLWW
1006.08.2022Augsburg0004FreiburgLW12Werder BremenLLLLWLWDDL
1106.08.2022Dortmund1010Bayer LeverkusenWL4Union BerlinDDWWDLWLDL
1207.08.2022Stuttgart1111RB LeipzigDD18Hertha BerlinWLLDWLLDLL
1307.08.2022FC Koln0031SchalkeWL17BochumDLLWLLDDWL
1412.08.2022Freiburg1013DortmundLW8MainzLWDDWLDWDW
1513.08.2022Werder Bremen1122StuttgartDD10B. MonchengladbachWLWDDLDDLL
1613.08.2022RB Leipzig1122FC KolnDD14HoffenheimWLWWLLDWLL
1713.08.2022Hoffenheim2232BochumWL13AugsburgWDLDWDLDLL
1813.08.2022Hertha Berlin1011Eintracht FrankfurtDD5FreiburgLWLDWWWDDW
1913.08.2022Bayer Leverkusen1112AugsburgLW2DortmundWWWWWDDLDW
2013.08.2022Schalke1022B. MonchengladbachDD6Bayer LeverkusenLWWWWDDWWD
2114.08.2022Mainz0000Union BerlinDD16StuttgartWDLLWLDWLD
2214.08.2022Bayern Munich2020WolfsburgWL3RB LeipzigWWLWWWLWLL
2319.08.2022B. Monchengladbach1010Hertha BerlinWL11FC KolnLDDLLWWWLD
2420.08.2022Wolfsburg0000SchalkeDD15SchalkeWWLDWWLLDW
2520.08.2022Stuttgart0101FreiburgLW
Home Away
Cell Formulas
RangeFormula
CT7:CX24CT7=LEFT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($C$5:$C$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
CY7:DC24CY7=RIGHT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($H$5:$H$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
I5:I25I5=MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
CT7:DC24Expression=CT7="D"textNO
CT7:DC24Expression=CT7="L"textNO
CT7:DC24Expression=CT7="W"textNO
 
Upvote 0
Peter_SSs said:
Did you try the suggestion that I made in post #16 which does use functions available in Excel 2016?

In relation to the Home/Away last 5, still trying to keep the formulas simpler by using a helper column, and including the green/red/yellow for W/L/D you could try this.

VKiprijan.xlsm
BCDEFGHIBPBQCTCUCVCWCXCYCZDADBDCDD
3RoundGerman Bundes League
4DateHome1st Half2nd HalfAway
505.08.2022Eintracht Frankfurt0516Bayern MunichLWHomeAway
606.08.2022Wolfsburg1222Werder BremenDDRankingTeam
706.08.2022Union Berlin1031Hertha BerlinWL9Eintracht FrankfurtDDDDWLLLLD
806.08.2022Bochum1112MainzLW1Bayern MunichWDWWWWLWLW
906.08.2022B. Monchengladbach1131HoffenheimWL7WolfsburgWDDDDLWLWW
1006.08.2022Augsburg0004FreiburgLW12Werder BremenLLLLWLWDDL
1106.08.2022Dortmund1010Bayer LeverkusenWL4Union BerlinDDWWDLWLDL
1207.08.2022Stuttgart1111RB LeipzigDD18Hertha BerlinWLLDWLLDLL
1307.08.2022FC Koln0031SchalkeWL17BochumDLLWLLDDWL
1412.08.2022Freiburg1013DortmundLW8MainzLWDDWLDWDW
1513.08.2022Werder Bremen1122StuttgartDD10B. MonchengladbachWLWDDLDDLL
1613.08.2022RB Leipzig1122FC KolnDD14HoffenheimWLWWLLDWLL
1713.08.2022Hoffenheim2232BochumWL13AugsburgWDLDWDLDLL
1813.08.2022Hertha Berlin1011Eintracht FrankfurtDD5FreiburgLWLDWWWDDW
1913.08.2022Bayer Leverkusen1112AugsburgLW2DortmundWWWWWDDLDW
2013.08.2022Schalke1022B. MonchengladbachDD6Bayer LeverkusenLWWWWDDWWD
2114.08.2022Mainz0000Union BerlinDD16StuttgartWDLLWLDWLD
2214.08.2022Bayern Munich2020WolfsburgWL3RB LeipzigWWLWWWLWLL
2319.08.2022B. Monchengladbach1010Hertha BerlinWL11FC KolnLDDLLWWWLD
2420.08.2022Wolfsburg0000SchalkeDD15SchalkeWWLDWWLLDW
2520.08.2022Stuttgart0101FreiburgLW
Home Away
Cell Formulas
RangeFormula
CT7:CX24CT7=LEFT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($C$5:$C$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
CY7:DC24CY7=RIGHT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($H$5:$H$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
I5:I25I5=MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
CT7:DC24Expression=CT7="D"textNO
CT7:DC24Expression=CT7="L"textNO
CT7:DC24Expression=CT7="W"textNO
Click to expand...

Yes, I tried the solution posted in the Post #16 and it worked.

The formula in column "I" also worked but I have difficulties how to highlight these result letters with the conditional formatting. When I go to Conditional formatting>HIghlight Cells Rules>Text that Contains here I cannot put both the ranges CT7:DC24 and the condition =CT7="D" since I only have one line "Format cells that contain the text". Where do I need to put these parameters: =CT7="D","L" and "W"?

Thanks.
 
Upvote 0
VKiprijan said:
Yes, I tried the solution posted in the Post #16 and it worked.

The formula in column "I" also worked
Click to expand...
OK. Thanks for the confirmation.

VKiprijan said:
.. but I have difficulties how to highlight these result letters
Click to expand...
Try it this way. Remove any existing conditional formatting from CT7:DC24 and with that range selected try this "Use a formula .. " method instead.
You need a separate rule for each letter.

1684403070876.png
 
Upvote 0
Peter_SSs said:
OK. Thanks for the confirmation.


Try it this way. Remove any existing conditional formatting from CT7:DC24 and with that range selected try this "Use a formula .. " method instead.
You need a separate rule for each letter.

View attachment 91855
Click to expand...

Yes, I've put all three conditions separately and it finally works perfect.

Thank you so much.
 
Upvote 0
Peter_SSs said:
Do you mean in my results? You listed those blue results as the correct result for Bayern Munich and that is exactly what my results showed:

View attachment 91681

Having said that, I have realised that I used functions in my formulas that you will not have in your 2016 version.

Here is an alternative, still using a helper column (which you can hide) to keep the formulas a bit shorter, that I think should work in your version.

VKiprijan.xlsm
BCDEFGHIBPBQCTCUCVCWCX
3RoundGerman Bundes League
4DateHome1st Half2nd HalfAway
505.08.2022Eintracht Frankfurt0516Bayern Munich|Eintracht Frankfurt|L|Bayern Munich|WLast 5 games
606.08.2022Wolfsburg1222Werder Bremen|Wolfsburg|D|Werder Bremen|DRankingTeam
706.08.2022Union Berlin1031Hertha Berlin|Union Berlin|W|Hertha Berlin|L9Eintracht FrankfurtLDLDL
806.08.2022Bochum1112Mainz|Bochum|L|Mainz|W1Bayern MunichWWLDW
906.08.2022B. Monchengladbach1131Hoffenheim|B. Monchengladbach|W|Hoffenheim|L7WolfsburgLWWDL
1006.08.2022Augsburg0004Freiburg|Augsburg|L|Freiburg|W12Werder BremenLLWLD
1106.08.2022Dortmund1010Bayer Leverkusen|Dortmund|W|Bayer Leverkusen|L4Union BerlinLDWDL
1207.08.2022Stuttgart1111RB Leipzig|Stuttgart|D|RB Leipzig|D18Hertha BerlinWLLLL
1307.08.2022FC Koln0031Schalke|FC Koln|W|Schalke|L17BochumLDLDL
1412.08.2022Freiburg1013Dortmund|Freiburg|L|Dortmund|W8MainzLLWDD
1513.08.2022Werder Bremen1122Stuttgart|Werder Bremen|D|Stuttgart|D10B. MonchengladbachWLLDW
1613.08.2022RB Leipzig1122FC Koln|RB Leipzig|D|FC Koln|D14HoffenheimWLLDW
1713.08.2022Hoffenheim2232Bochum|Hoffenheim|W|Bochum|L13AugsburgWDDLL
1813.08.2022Hertha Berlin1011Eintracht Frankfurt|Hertha Berlin|D|Eintracht Frankfurt|D5FreiburgLWWWL
1913.08.2022Bayer Leverkusen1112Augsburg|Bayer Leverkusen|L|Augsburg|W2DortmundWDWDW
2013.08.2022Schalke1022B. Monchengladbach|Schalke|D|B. Monchengladbach|D6Bayer LeverkusenLDWDW
2114.08.2022Mainz0000Union Berlin|Mainz|D|Union Berlin|D16StuttgartLWDDW
2214.08.2022Bayern Munich2020Wolfsburg|Bayern Munich|W|Wolfsburg|L3RB LeipzigWWLWW
2319.08.2022B. Monchengladbach1010Hertha Berlin|B. Monchengladbach|W|Hertha Berlin|L11FC KolnWLWDW
2420.08.2022Wolfsburg0000Schalke|Wolfsburg|D|Schalke|D15SchalkeWWLWL
2520.08.2022Stuttgart0101Freiburg|Stuttgart|L|Freiburg|W
2620.08.2022Dortmund1023Werder Bremen|Dortmund|L|Werder Bremen|W
Sheet3
Cell Formulas
RangeFormula
CT7:CX24CT7=LEFT(TRIM(RIGHT(SUBSTITUTE(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/ISNUMBER(SEARCH("|"&$BQ7&"|",$I$5:$I$500)),COLUMNS($CT:CT))),"|"&$BQ7&"|",REPT(" ",100)),100)),1)
I5:I26I5=IF(B5="","","|"&C5&"|"&MID("LDW",SIGN(F5-G5)+2,1)&"|"&H5&"|"&MID("WDL",SIGN(F5-G5)+2,1))
Click to expand...

What formula in the helper column "I" should I put to show me only the results for the games that have been played not for all the games in the fixtures? Otherwise for the games that were not played yet it shows me "DD".

Thanks in advance.
 
Upvote 0
VKiprijan said:
What formula in the helper column "I" should I put to show me only the results for the games that have been played
Click to expand...
Well, you could use ..
Excel Formula: 
=IF(B5="","",MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1))

But you could also keep the existing formula and simply hide column I since it is just a helper column for the other formulas.
 
Upvote 0
Peter_SSs said:
Well, you could use ..
Excel Formula: 
=IF(B5="","",MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1))

But you could also keep the existing formula and simply hide column I since it is just a helper column for the other formulas.
Click to expand...

But, I don't want to use the B column which is used for dates of the games, I would rather want to use columns F and G (final result) as a main indicator that a game was played. How do I use these two columns in the formula, before the MID i tried to insert =IF(F<>"",G<>"" but it didn't worked.

Thanks.
 
Upvote 0
VKiprijan said:
i tried to insert =IF(F<>"",G<>""
Click to expand...
Why would you need to check both columns? Surely if a game has not been played, neither column would have a number in so just checking one column for blank should be sufficient?
 
Upvote 0
Peter_SSs said:
Why would you need to check both columns? Surely if a game has not been played, neither column would have a number in so just checking one column for blank should be sufficient?
Click to expand...
OK, you're right, I could use just the column B as a main indicator if the game was played, otherwise it will be just overcomplicating things.

Thanks a lot.
 
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
336
VKiprijan
V
J
  • Solved
conditional format to change color
2
Replies
10
Views
223
SanjayGMusafir
SanjayGMusafir
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
600
felixstraube
F
A
  • Question
Fetch heads up stats between 2 teams
Replies
2
Views
126
AYouQueTai
A
G
  • Question
Sorting data from another sheet to display in a main page
Replies
0
Views
315
Gonney
G

Forum statistics

Threads
1,215,107
Messages
6,123,126
Members
449,097
Latest member
mlckr

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