How to Create a Sports League Table | Beginner Excel Tutorial
HTML-код
- Опубликовано: 30 июл 2024
- Access the file and become a LAD: / excelladz
Post any questions in the comments section lads.
Timestamps
Intro: 00:00 - 1:01
Step 1 (Home/Away Points): 1:02 - 4:23
Step 2 (COUNTIFS Function): 4:24 - 11:06
Step 3 (SUMIF Function): 11:07 - 14:57
Step 4 (Create Ladder): 14:58 - 19:42
Outro: 19:43 - 20:00
THIS IS THE BEST EXCEL TUTORIAL I HAVE EVER WATCHED
Great Tutorial. Used this to make our Warhammer 40k League more automated. Thank you very much!
Great Video Lad! I work for the FC Dallas Youth system and I used this for our tournaments that we host. Very well explained and easy to create. My Director of Coaching thinks I am an excel wizard thanks to you lol.
Fantastic! Thanks for watching lad 🔥
Huge Help. Thanks for taking the time to make this video.
Your videos have taught me a lot so far. Simple things like named cells, and named range, I really wish I learned long ago. Thank you.
this is the best tutorial about making leagues that i ever came across, thanks a lot sir. Its my second time here, first was last year
Great Tutorial, really enjoyed this and all the other videos on your channel.
@13:30 - Cell I2 - The points column. You could also use this formula to calculate the team's points: C2*3+D2.
Amazing video, just used it to make the overall table of Copa América since I couldn't find it anywhere and it worked flawlessly, so well explained :) Thanks a lot
Hi there!! thanks for this video. Much love and support for the work you have done and the ease with which you have explained the entire thing. btw, this video is helping football fans like me in India. Kudos for that.
I need help. I'm on Google Sheets and when I autofill for the home points formula the J column moves down as well for example if it's C22 it'll say J22 not J2 like I want it to if Home team wins. Is there a way to fix this or is it just Excel that can do this?
How's it going?! Thanks for this tutorial! I was fiddling on last Friday night trying to sort out a league table for my amateur footy league (Not the Aussie Footy). This has helped me heaps. I'm trying to get into the field of data analytics and generally think I'm fairly competent on Excel, but this tutorial has proven me wrong. Top lad!
Keep working hard lad 🔥
did you join to use the sheets?
Hy man. Thanks for the video. I'm using Excel 2019 and i don't have "sequence" function. How do I manage to solve the problem with the table after we create it? To sort clubs after points, goals for, goals againts etc? Thank you
How do you change your sort code to add multiple columns to sort but some are ascending and some are descending?
Good morning, I cannot see your functions as the words shown on the videos are too small. How do I do the if function for the points?
Im having some trouble with my table. it isnt calculating how many matches have been played for each team and also the goals difference is calculating correctly. one team has more goals then another but its putting this team 3rd instead of 2nd. can anyone help? this is on Google Docs. thanks
This has been super helpful to create league table for our local league, thank you! One question - the rules of our league give 1 point for scoring 75% of the winnings team score. How do I add this into the formula please? I've tried playing about, but nothing is successful so far, any help would be much appreciated! :)
Thanks for the tutorial, my league table turned out great! I’m just curious as to when you’ll show us how to change the table by matchday (what you’re showing at the end of the video). I would love to have this feature on my spreadsheet!
on which excel version is done pls?, cause i cant do it on 2016
Very useful video :)
Thanks lad.
How do I get the dataset pls?
Hey i am wondering what the formula is for the home/away points?
Thanks a lot for this video. Just a question. If i want to have a "position" column depending on points, score... how to do it? i mean... lets say two teams have exactly same points, score and goal difference? how do u do it? Thanks.
Unbelievably helpful video. Are you able to make a video on how to show the ladder round by round? Can't seem to find it on your channel. Cheers!
i have a problem where with the home/away points the cell im telling the function to get moves down with the other cells
Hi. Is there a way to calculate each team's attack and defense level between 10 and 200. I want to give Premier League teams their levels all the way down to League Two. And of course the League Two champions' levels should be quite a lot lower than the Premier League champions' levels. Please upload a video on it
I've done a few of these from other videos and I'm impressed you haven't used Name Manager. I've got a 35 league workbook which incorporates everything from the PL, EFL, NL, right down to the regional leagues - plus cups - but my next move is to try and consolidate these tabs into one tab. 35 leagues means 35 tabs, plus cups. There must surely be a way to have fixtures for, say, 4 leagues in one tab, then level 5 to 7 (7 leagues) into the next tab and have cups running alongside these fixtures. I'll give your video a go over the next few weeks and see how it goes.
Great lad, and thanks for the support! Let me know how you go. If you have any questions or video ideas, please reply to this comment as I am looking to expand the business.
Thanks for a great video. Years ago, I created an excel for the PL, but in a less elegant way.
(1) How would you go about a line chart, say, showing the top 6 teams, over the entire season but on a week by week basis
(2) Keeping track of the top scorers, again on a weekly (match day) basis, and lastly
(3) now some teams get points deduction for breaking fair play rules. How should I handle that.
Hi! i dont understand why doesn t work the formula from points! Every formulas works greats, but when i want to calculate Point, doesn t work
Many thanks for this great model. Will this model work in a Google sheet?. Is it also possible to have autogenerated fixtures and populate league standings sorted by date (month) because I want to use it for a private league. Finally, I also want to have dashboard that displays team streaks of winning/loosing/drawing. I will be expecting a favorable response from you.
Hi lad, so my next video is creating a model that will autogenerate fixtures in Excel 🔥 . That'll be uploaded in the next couple days hopefully. As for the dashboards, I can always build on the video and show everybody some techniques in displaying a team's form 👍
This is an amazing video, wow. I am just having one error. Everything works fine except i seem to be having some trouble when goals for/against. it seems to work fine for some of my teams but not others. all formulas were absolute referenced etc but can't seem to get it right. any ideas?
Hi lad, it depends on the error. Some versions of Excel don't have access to XLOOKUP, so the VLOOKUP function is required instead. Based on your comment, it doesn't seem to be the issue 👍 I would double check that the criteria you are searching for is in the lookup range, otherwise the XLOOKUP has nothing to return... 💪
How to sort the tables when head-to-head matches are decisive?
What is the formula for making positions
Also my sort array didn’t work at the end is that bc I manually input the team names so they have no corresponding data and bc of my shortcut for the points formula?
Hi lad, can you tell me exactly what happens/appears when you try to use the SORT Function to get the Final League Table? That way it would be slightly easier to pinpoint your mistake lad 👍 It shouldn’t be because of your shortcut in the points formula, but you can always try the method explained in the video and see if that works for some reason 💪
@@excel_ladz thanks for replying bro 👊 the error its returning is “SORT range must be a single row of a single column”
Hey mate, great video tutorial. One thing I’m struggling with at the moment is that my wins, draws, and losses values aren’t appearing to update. Is there any way of fixing this? Also, when I open up the excel file, it comes up with a message which says my workbooks need to be updated. Is this an issue by any chance? Thanks again!
Thanks lad 🔥 It’s very unusual for the ‘Update’ message to appear building this spreadsheet, as it doesn’t use a Power Query to import website data… Maybe Excel is asking you to download the newest version 🤔 To fix your issue, try playing around with each COUNTIFS function. Try and isolate the problem by changing values and seeing if the COUNTIFS function reacts… Or, your issue may be that your version of Excel does not support the SORT function. Let me know how it goes lad 👍
Hey mate, I figured out my issue with the values not showing up! It turns out that I typed in the wrong table name for giving the 2nd criteria, which is why the values weren’t updating. I’ll know for next time though! Thanks again for your help, I’m just a little dyslexic at times xD.
hi, did you subscribe to use the sheets?
When I put -1 in the sort function it gives me an error
Great video bro ! it helps me a lot ! ... but its untilat minute 16:18, when you say "... I AM GOING TO SPILL THE AMOUNT OF TEAMS..." - i followed carefully every step, but my data didnt "spill".... can you help me ? thanks. love your videos
Hi lad, “spill” is getting to the dynamic nature of the SEQUENCE function, which “spills” an array. This function is only available on Excel 2019 and newer. As a result, your errors may be appearing due to your Excel version’s inability to support array functions such as SEQUENCE and SORT. To fix this, I recommend watching my video titled “Create fixture list and league table in Excel” video. This will explain how to build a league table in the same format, but using functions every version of Excel has access to 👍
practical for betting sports
How can I predict the position of a team using the past 10 year data for the same team like their past position, points, goal scored, goal conceded, wins, draw, lose ect.
Hi lad, if I understand correctly, you want to simulate (and thereby predict)a whole season using a team’s past data. To do this simply, collect a running average of the team’s attack and defence strength. Then, do this for every other team in the league. Once that is done, it is possible to use a simulation technique, such as using the BINOM.INV function to simulate every game in the schedule, based off of each team’s strength. Then, you would compile every team’s positions; which you could simulate more times to see a distribution of results.
Thanks for watching lad, I plan to release a video on this concept very soon 🔥
can you help me at all mate, i got this to work on excel but on google sheets it doesnt work, I get the table & results fine, sorted that, but when i try to sort the table with your function, it doesnt work for the sheet, i tried =sort function, still wouldnt work, tried sorting by ranges & wouldnt work, got it to sort when i did copy & paste but i need it to sort automatically, only difference then is the formulas updated the starting table, not sure how to get the final table formatted on google sheets
I assume you got this fixed by now but for others looking for this answer you would enter it like this in googlew sheets: =SORT(B25:J34,9,False,8,False,6,False) or change False to True if you want it assending instead
@@sclibbon I did another method mate, I have to with it and then create a new way in excel and sent to sheets and worked but thank you for replying
Hello. I'm having trouble with the SORT function. Any clues?
Hi lad, SORT is one of the new array functions introduced by Excel. So, if you’re following everything I’ve done in the video and you still have no luck, it might be because your version of Excel doesn’t actually support SORT. This would be apparent if you’re getting a #NAME? error 👍
Thanks for the tutorial bro! For your points formula, can’t you just do (Wx3)+D
Thanks for watching lad 🔥 Absolutely! For the sake of the video I wanted to do things the longer way, so that if subscribers had any errors it would be easier to spot for them… I’m glad you’ve taken an interest in the model lad 👍
Mm interesting. I’m in a league prediction with 12 friends. Home win 2 points, away win 3 points draw 4 points. If correct score add 5 points if over 4 goals add other 5 points.
Great video bro👍🏻. One question are these the formulas for the latest updated version of excel?
Hi lad, yea they are 👍 Although, if you have an older version of Excel, you can follow the instructions at the end of this video in order to make a League Table: m.ruclips.net/video/tV7BnDhb-i0/видео.html&pp=ygUdRXhjZWwgbGFkeiBmaXh0dXJlcyBnZW5lcmF0b3I%3D
@@excel_ladz 💯 thanks bro
The Sum if function doesnt work for me
Hi I would to see a video on a volleyball league tournament
Really great video. Unfortunately the commands don't work for two fields, but they do for the rest
Thanks for watching lad 🔥 Yes, the SEQUENCE and XLOOKUP functions are only available to Excel 2019 and newer. Have a look at the second half of the “Creating Fixtures and League Table” video on my channel where the commands are available for all users 👍
I don't understand why you do this (at least in my view) not needed step with "Home points" and "Away points". There is a three-way-betting called 1, X, 2 for Home, draw, and Away you could use instead! Then you only need one additional column to the matches. You can also easily count how many games were won (home and on the road) and how many draws and losses were reached (as well home and away)!
Another information could be interesting for the 38 rounds. 19 were the first part and from 20 to 38 were the second part.This way you could see in 5 tables how a team got its points.
Personally I'd always do the home and away tables first because it allows me to do these COUNTIFS only once fot each (win, draw, loss). I then simply add up the wins, drasws and losses to reach played matches!
what about the icons of the clubs?
Hi lad, you could include the icons using the new IMAGE function 🔥
Does anyone have a link for a file that works
Hi lad, I recommend checking out the model on Patreon here: patreon.com/ExcelLADZ
@@excel_ladz yo boss man i’m not tryna spend some bread on a spread sheet any way i can get it for free?
@@alexprovost4005Fair lad, I recommend following the steps in the video. If the functions don't work (i.e. your version of Excel is 2016 or later), then have a look at the second half of my most recent video where I create a league table 👍
How to create Olympic Medal Ranking using Excel?
Thanks for asking @Kucing Comey. I would have all the events you wanted to include in your 'Medal Ranking' table formatted in columns across like this: Discipline, Event, Gold Medal, Silver Medal, Bronze Medal (and if you wanted to include 4th, 5th, ... you can). This would be in its own worksheet perhaps titled 'Events'.
Then when creating the Olympic Medal Ranking table in another worksheet, your 'y-value' on the left-hand side are the countries in your data (which can be found by using the UNIQUE function as in the video), and the column headers along the top are Gold Medal, Silver Medal, Bronze Medal. For the first cell in the Gold Medal column and beside the first country in your list, use the COUNTIF function (range being the list of gold medal teams in the worksheet 'Events', and criteria being the country beside the cell). Autofill this down to your bottom country and do the same process for Silver Medal and Bronze Medal columns.
Then, you can sort this array by creating another table and using the same method I used in my video (The SORT function). Remember to use the curly brackets to list the order in which you want to sort by - Gold Medals, then Silver Medals, then Bronze Medals. Finally, make sure you write '-1' at the end of the formula before closing the brackets so that it is ordered in descending order (meaning the team with the most gold medals is ranked first).
Hope this helps.
When is the "Ladder by round" video coming up?
Very soon lad, stay tuned 🔥
Excellent, im tuned😍
share the spreadsheet
can you buy these?
You most certainly can lad 🔥 Here’s the link: www.patreon.com/posts/create-sports-84701604
Hi there!
I am making a competition with some friends by guessing the result of each football match through a season. If you guess correct "Win/Draw/Defeat" you get 1 point and if you guess correct "Win/Draw/Defeat" and the correct goals scored in the match, you get 2 points. These points has been made into a table in excel. The table will be updated after each round has been played. The table uses RANK and INDEX.MATCH to calculate who is 1st 2nd 3rd and so on...
Is it somehow possible to do this?
- If a person climbs on the table it will show a green arrow and a number beside how many "rows" the person has climbed? For example if the person on 6th place climbs to 4th place, it will show a green arrow and the number "2" beside the arrow.
- If a person falls on the table it will show a red arrow and a number beside how many "rows" the person has fallen. For example if the person on 7th place falls to 8th place, it will show a red arrow and the number "1" beside the arrow.
- If a person stays on the same place it will show a yellow arrow pointing to the right.
I hope this was understandable and i hope it is a formula for this :D.
Thank you!
Thanks for commenting lad. Obviously I can't give you an exact formula, but I can give you a couple ideas on how to build this in Excel.
Off the top of my head, you could use the SUMIF and OFFSET functions. You would use the SUMIF function to add each person's points in the competition. Then, perhaps by using the OFFSET function within the SUMIF function, you may be able to fund a person's points as of a particular round in the competition. Then, as you said, using the RANK.EQ function you can work out how much a player has fallen or risen in the rankings.
Also, for the arrows you were talking about. You can access those by going into 'conditional formatting', then clicking on 'Icon Sets', then clicking on the 3 Arrows.
Thank you for your fast response, i will try what you wrote :)
Also I am trying to learn more about excel, so I made this league table you show in this video, and everything seemed to work fine except the last formula for sorting the table.
Just to inform you I am doing this on a mac so where you put your "," i need to put a ";".
I tried to use this formula: =SORT(B24:J39;(9;8;6);-1)
Excel does not accept that formula...
Excel changes it to: =SORT(B24:J39;(9,86);-1)
Because of this, the table only sorts by the points and not goal difference and goals scored.
if i have understood how the formula works, its not that strange the formula dont work as i want to, because it first sorts by column 9 and then by column 86.
Do you have any advice for whats wrong and/or how i can fix it?😀
@@henninghansen4073 I use Excel on Mac too and I use a ","... With the formula that wasn't accepted, make sure you use the "curly" brackets which are these: { }. So your formula should work if it's: =SORT(B24:J39,{9,8,6},-1) Try that, and let me know how it goes lad!
@@excel_ladz
Thanks man!
I did not use the curly brackets… I will try that when i come home and I will for sure let you know😊
Also I do not know why I need to use «;» instead of «,». But it doesnt matter for me, I am now used to use the «;»😊
@@excel_ladz
Thank you so much, it did work with the formula "=SORT(B24:J39;{9;8;6};-1)"😍
it doesnt count the away games only col b2
G'day lads, if you want access to this model (and every other Excel LADZ model), join the Excel LADZ community! Sign up here: www.patreon.com/ExcelLADZ 💪
cant for the love of me get the grid to work points out
GUYS AM FAILING TO UNDERSTAND HOW TO DO ABSOLUTE REFERENCE. SHORTCUT
Try F4 , in some other computer it's second function (fn key) then f4