Formula to count % of goals in a total number of games

V

VKiprijan

New Member
Joined
Feb 24, 2021
Messages
40
Office Version
  1. 2016
Platform
  1. Windows
Hello to All,

I have a spreadsheet of soccer matches in which I would like to build a table (columns AO thru AR) to look like a top list of soccer teams with the highest percentage of goals that those teams had yielded over the number of games they played so far.
The number of games should be automatically calculated after each round is played.

Any idea of which formulas I should use in order to get this done?


Thanks everyone in advance.

Vlad
 

Attachments

  • Image1.jpg
    Image1.jpg
    235.4 KB · Views: 19
  • Image2.jpg
    Image2.jpg
    183.8 KB · Views: 17

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hello. I think it would help you get suggestions if you ..
  • Manually filled in the expected results for some sample data
  • provided the sample data and those expected result in a way we could easily copy** to test suggestions as there appears to be a great deal of manual typing otherwise. :eek:
  • Explain, in relation to the specific sample data, how you got those expected results manually.
** MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Hello Peter,

Thanks a lot for your quick reply on this topic. I have tried to copy/paste values and formulas from this Excel sheet but due to the number of cells it would not let me post it in this topic using the XL2BB Add-in.
I wish there was an option to upload the file with all the formulas that I had used to calculate the statistical data for all these games. Let me try to explain it in a little bit more detail:

1729686235082.png


Let's take Dortmund, for example, which plays home and away games and sometimes in those games there are at least 3 or more goals (3+) scored by both teams.
In one season, which consists of 34 games, Dortmund played in 21 games where there were "3+" which is a 61.7%.

So, how do we create a formula that will show which team had played the most games with 3+ goals scored?

I hope that this will help but if there is no way without XL2BB to show maybe I can send you the Excel file which is small (only 139Kb).

Please let me know whatever suits you better.

Thanks.
Vlad
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to count % of goals in a total number of games
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
VKiprijan said:
due to the number of cells it would not let me post it in this topic using the XL2BB Add-in.
Click to expand...
Perhaps a much smaller dummy set of data with a lot less teams and a lot less rounds?
(.. that is unless you already have an answer at the other forum where I am not monitoring your thread)
 
Upvote 0
Peter_SSs said:
Perhaps a much smaller dummy set of data with a lot less teams and a lot less rounds?
(.. that is unless you already have an answer at the other forum where I am not monitoring your thread)
Click to expand...
RoundResultTotal Goals
DateHome1st Half2nd HalfAwayPOINTS45'90'KT1 PP2 PP0 - 1 0 - 22 - 3 2 - 43 - 54 - 62+3+4+5+7+GGGG3+GG4+1 GG2 GGG 1PPG 2PPVG X 1PPOG D/GDP2+ 1PP2+ 2PP
05.08.2022Eintracht Frankfurt0516Bayern MunichLW03222522+3+4+5+7+GGGG3+GG4+2GG3+2+1>2GG3+DP2+ 1PP2+ 2PP
06.08.2022Wolfsburg1222Werder BremenDD112XX312-43-54-62+3+4+GGGG3+GG4+1GG2GG3+1+1>2GG3+2+ 1PP2+ 2PP
06.08.2022Union Berlin1031Hertha BerlinWL30111132-43-54-62+3+4+GGGG3+GG4+2GG1+3+2>1GG3+DP2+ 2PP
06.08.2022Bochum1112MainzLW03X22212-32-43-52+3+GGGG3+1GG2GG2+1+1>2XGG3+2+ 1PP2+ 2PP
06.08.2022B. Monchengladbach1131HoffenheimWL30X11222-43-54-62+3+4+GGGG3+GG4+1GG2GG2+2+1=2XGG3+2+ 1PP2+ 2PP
06.08.2022Augsburg0004FreiburgLW03X22042-43-54-62+3+4+1+3+2>1XDNDG2+ 2PP
06.08.2022Dortmund1010Bayer LeverkusenWL30111100-10-21+NG1>2GNDG
07.08.2022Stuttgart1111RB LeipzigDD11XXX200-22-32-42+GG1GG2GG2+NG1>2XGG2+ 1PP2+ 2PP
07.08.2022FC Koln0031SchalkeWL30X11042-43-54-62+3+4+GGGG3+GG4+2GG1+3+2>1XGG3+2+ 2PP
12.08.2022Freiburg1013DortmundLW03122132-43-54-62+3+4+GGGG3+GG4+2GG1+3+2>1GG3+2+ 2PP
13.08.2022Werder Bremen1122StuttgartDD11XXX222-43-54-62+3+4+GGGG3+GG4+1GG2GG2+2+1=2XGG3+2+ 1PP2+ 2PP
13.08.2022RB Leipzig1122FC KolnDD11XXX222-43-54-62+3+4+GGGG3+GG4+1GG2GG2+2+1=2XGG3+2+ 1PP2+ 2PP
13.08.2022Hoffenheim2232BochumWL30X11413-54-62+3+4+5+GGGG3+GG4+1GG2GG3+1+1>2XGG3+2+ 1PP2+ 2PP
13.08.2022Hertha Berlin1011Eintracht FrankfurtDD111XX110-22-32-42+GG2GG1+1+1=2GG2+ 2PP
13.08.2022Bayer Leverkusen1112AugsburgLW03X22212-32-43-52+3+GGGG3+1GG2GG2+1+1>2XGG3+2+ 1PP2+ 2PP
13.08.2022Schalke1022B. MonchengladbachDD111XX132-43-54-62+3+4+GGGG3+GG4+2GG1+3+2>1GG3+2+ 2PP
14.08.2022Mainz0000Union BerlinDD11XXX000-10-21+NG1=2XNG
14.08.2022Bayern Munich2020WolfsburgWL30111200-22-32-42+2+NG1>2GNDG2+ 1PP2+ 2PP
 
Upvote 0
Hi Peter,

I just copied and pasted the table in XL2BB, please let me know if you can now see the formulas.

Thanks in advance.
Vlad
 
Upvote 0
Soccer League v2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
3RoundResultTotal Goals
4DateHome1st Half2nd HalfAwayPOINTS45'90'KT1 PP2 PP0 - 1 0 - 22 - 3 2 - 43 - 54 - 62+3+4+5+7+GGGG3+GG4+1 GG2 GGG 1PPG 2PPVG X 1PPOG D/GDP2+ 1PP2+ 2PP
505.08.2022Eintracht Frankfurt0516Bayern MunichLW0322252      2+3+4+5+7+GGGG3+GG4+ 2GG3+2+1>2 GG3+DP2+ 1PP2+ 2PP
606.08.2022Wolfsburg1222Werder BremenDD112XX31   2-43-54-62+3+4+  GGGG3+GG4+1GG2GG3+1+1>2 GG3+ 2+ 1PP2+ 2PP
706.08.2022Union Berlin1031Hertha BerlinWL3011113   2-43-54-62+3+4+  GGGG3+GG4+ 2GG1+3+2>1 GG3+DP 2+ 2PP
806.08.2022Bochum1112MainzLW03X2221  2-32-43-5 2+3+   GGGG3+ 1GG2GG2+1+1>2XGG3+ 2+ 1PP2+ 2PP
906.08.2022B. Monchengladbach1131HoffenheimWL30X1122   2-43-54-62+3+4+  GGGG3+GG4+1GG2GG2+2+1=2XGG3+ 2+ 1PP2+ 2PP
1006.08.2022Augsburg0004FreiburgLW03X2204   2-43-54-62+3+4+       1+3+2>1XDNDG  2+ 2PP
1106.08.2022Dortmund1010Bayer LeverkusenWL30111100-10-2              1+NG1>2 GNDG   
1207.08.2022Stuttgart1111RB LeipzigDD11XXX20 0-22-32-4  2+    GG  1GG2GG2+NG1>2XGG 2+ 1PP2+ 2PP
1307.08.2022FC Koln0031SchalkeWL30X1104   2-43-54-62+3+4+  GGGG3+GG4+ 2GG1+3+2>1XGG3+  2+ 2PP
Bundes League
Cell Formulas
RangeFormula
H5:H13H5=IF(C5="","",MID("LDW",SIGN(E5-F5)+2,1)&MID("WDL",SIGN(E5-F5)+2,1))
I5:I13I5=IF(E5="","",IF(E5>F5,3,IF(E5=F5,1,0)))
J5:J13J5=IF(F5="","",IF(F5>E5,3,IF(F5=E5,1,0)))
K5:K13K5=IF(C5=""," ",IF(C5-D5>0,1,IF(C5-D5<0,2,"X")))
L5:L13L5=IF(E5=""," ",IF(E5-F5>0,1,IF(E5-F5<0,2,"X")))
M5:M13M5=IF(E5="","",IF(E5=F5,"X",IF(E5<F5,2,IF(E5>F5,1,0))))
N5:N13N5=IF(AND(C5<>"",D5<>""),C5+D5,"")
O5:O13O5=IF(AND(E5<>"",F5<>""),(E5+F5)-(C5+D5),"")
P5:P13P5=IF(AND(E5<>"",F5<>"",E5+F5<=1),"0-1","")
Q5:Q13Q5=IF(AND(E5<>"",F5<>"",E5+F5<=2),"0-2","")
R5:R13R5=IF(E5+F5=2,"2-3",IF(E5+F5=3,"2-3",""))
S5:S13S5=IF(E5+F5=2,"2-4",IF(E5+F5=3,"2-4",IF(E5+F5=4,"2-4","")))
T5:T13T5=IF(E5+F5=3,"3-5",IF(E5+F5=4,"3-5",IF(E5+F5=5,"3-5","")))
U5:U13U5=IF(E5+F5=4,"4-6",IF(E5+F5=5,"4-6",IF(E5+F5=6,"4-6","")))
V5:V13V5=IF(E5+F5="","",IF(E5+F5>=2,"2+",""))
W5:W13W5=IF(E5+F5="","",IF(E5+F5>=3,"3+",""))
X5:X13X5=IF(E5+F5="","",IF(E5+F5>=4,"4+",""))
Y5:Y13Y5=IF(E5+F5="","",IF(E5+F5>=5,"5+",""))
Z5:Z13Z5=IF(E5+F5="","",IF(E5+F5>=7,"7+",""))
AA5:AA13AA5=IF(AND(E5>0,F5>0),"GG","")
AB5:AB13AB5=IF(AND(C5+E5>0,D5+F5>0,E5+F5>2),"GG3+","")
AC5:AC13AC5=IF(AND(C5+E5>0,D5+F5>0,E5+F5>3),"GG4+","")
AD5:AD13AD5=IF(AND(C5>0,D5>0),"1GG","")
AE5:AE13AE5=IF(AND(E5>0,F5>0),"2GG","")
AF5:AF13AF5=IF(N5="","",IF(113<1,"NG",IF(N5<2,"1+",IF(N5<3,"2+","3+"))))
AG5:AG13AG5=IF(O5="","",IF(O5<1,"NG",IF(O5<2,"1+",IF(O5<3,"2+","3+"))))
AH5:AH13AH5=IF(AND(N5<>"",O5<>""),IF(N5<O5,"2>1",IF(N5>O5,"1>2","1=2")),"")
AI5:AI13AI5=IF(AND(C5<>"",D5<>"",C5=D5),"X","")
AJ5:AJ13AJ5=IF(N5="","",IF(E5=0,IF(F5=0,"NG","DNDG"),IF(E5>0,IF(F5>0,IF(E5+F5>=3,"GG3+","GG"),"GNDG"),"DNDG")))
AK5:AK13AK5=IF(OR(AND(C5>D5,E5>F5,E5>C5),AND(D5>C5,F5>E5,F5>D5)),"DP","")
AL5:AL13AL5=IF(C5+D5>=2,"2+ 1PP","")
AM5:AM13AM5=IF(E5+F5>=2,"2+ 2PP","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G5:G310,G311:H346Expression=F5>E5textNO
B5:B346Expression=E5>F5textNO
G5:G13Cell ValueduplicatestextNO
B5:B13Cell ValueduplicatestextNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
'How to' question about a Dashboard
Replies
5
Views
663
etaf
etaf
S
  • Question Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
513
Solon Aquila
S
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
536
awoohaw
awoohaw
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F
Z
  • Question Question
Calculation assistance to form an excel formula
Replies
7
Views
397
Zwax
Z

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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