Create an Automatic Football League Table in Excel | Alan Murray | Skillshare

Create an Automatic Football League Table in Excel

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Create an Automatic Football League Table in Excel

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
10 Lessons (54m)
    • 1. Introduction

    • 2. Setting up the Tables

    • 3. Calculate Games Played, Won and Lost

    • 4. Calculate Games Drawn, Goals For and Goals Against

    • 5. Calculate Points Earned and Goal Difference

    • 6. Rank Teams by Points

    • 7. Uniquely Rank Teams

    • 8. Rank Teams by Goal Difference then Goals Scored

    • 9. Populating the League Table

    • 10. What to do Next

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

In this class you will learn how to create a football league table in Excel. We will use the power of Excel to automatically calculate league table rankings as results are entered.

Whether your league table rankings are determined by goal difference, goals scored, number of away wins or something else. You will learn how you can use this criteria to automatically determine a team or players league position.

The spreadsheets used are available for download along the way for practice, or to use as a template to adapt to your needs.

In this class we focus on creating a league table for football/soccer. However these techniques can be used for any sports or business example when league tables and rankings are used.

For the class project you can create a league table for whatever scenario you wish.

Let's get started.

Meet Your Teacher

Teacher Profile Image

Alan Murray

IT Trainer, Coffee Lover and Lifelong learner


Hello, my name is Alan Murray and I have been training and consulting in Excel now for over 20 years. I have had the pleasure in helping individuals and businesses from all around the world improve their Excel skills.

I set up Computergaga about 10 years ago to help spread the latest Excel tips and to engage with other Excel experts and learners. It has grown beyond my expectations and I hope to get the chance to speak with many of you soon.

I have a real passion for Excel and I hope that it comes across in my classes. I believe that it helps if you enjoy what you are learning.

I currently live in Ipswich, on the east coast of England. I have two beautiful children. So they take up most of my time away from Excel. If I'm not with t... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.



1. Introduction: Hello. I'm Alan Murray on Welcome to this class on creating an automatic football league table in Excel. During this class, you'll be shown how to create this Football league table. This league table is ranking teams by points, then by goal difference and then by gold scored. And you can see that in this example Bayern Munich a top by points, Barzel above Monaco by goal difference and you can see Celtic on top of the four teams with one point because of goals scored. This league table is using the results coming from the fixture shaped. So if I was to enter a result right now, the Celtic to beat Liverpool one new and go back to my league table, it has automatically updated with the new data. Although this class focuses on a football league table, example, the techniques covered during the class can easily be adapted for other sporting league tables or even business table examples. There will be a download link in the project area of the class so that you think access to the exact files that I am using, and it can follow along with me at some point during the class during one of the videos. You may be asked to recommend this class for others. If you are enjoying the class, please click a thumbs up, I said it puts it out there for other people so they can get access to the same information that you are. And please leave a comment on the community board. If you've got any questions, you want any help? We just want to leave a nice comment. Please share in the community board. I do check the comments, and I will respond to it personally. But there are other people taking this class as well, who may be able to respond to it at before even I do on. Please complete the project at the end, which will be an example for you to create your own league table off your choice. And then you can share that in the class for feedback at from myself and maybe from others as well. I hope you enjoy the class, and I hope to speak to you soon. 2. Setting up the Tables: hello and welcome to the first lesson off this class on creating automatic league tables in Excel. And in this first lesson, we need to set up the tables off our spreadsheet. So let's begin with this team's sheet. So I have this team sheet which simply lists the 10 teams that I'm going to use for this example off eight football league table. Obviously, these could be. What have it seems you want? I'm going to highlight those team names. That's only to highlight the whole whole table. Actually, from a one there and on my home tab, I will click the format as table button and I'm going to choose one of these table styles. You can choose whichever style you wish. It has no relevance in what we're going to do. I might just go for this scream one here that would just double check the rains that are highlighted. A 1 to 11 with a hetero, which is all true zone and click OK on that table has been applied to my range. Now maybe not a great example in this one with these teams, but hopefully will become parent as the class goes on. That a reason on setting these up into tables. These two greater simplify the formulas when we get to it. I personally believe that will be a lot easier to write. The formulas were going to if their data is in tables as opposed to a static range with cell references like C to an A free and that kind of thing, I think we'll be a lot more difficult. And if you're new to table drawer, hopefully about to see that and possibly agree. Possibly. No, we don't need a table to do what we're going to do. Well, we have one. And it supplied a little bit of for Martin, which is not really necessary. You might like it. You might know I can cook anywhere in that table, and I will get this designs have at the top. So if I choose to, I can change that style right now. Include an option for no style. I'm going to keep my one now. Something very important is on the far left. We have the opportunity to name our table, and I am definitely going to do that for every table this table. I'm simply going to call teams that seems like the best name I could possibly give that no percent to confirm that rain just simply known as teams. Whenever I need it, the other thing I will do is click this filter button checkbooks along design because I don't think I'm gonna need to any filtering here. It's not doing any harm burning, and to get rid of that, it's really not necessary. And our first table is done. Let's move on to the next one. I have a fixtures sheet now. You can quite the fixtures, however you wish. We don't talk about that on this class. There are loads of websites you can find online. If you google this stuff, which you know various systems that will generate a fixture list for you, depending on your kind of parameters, just kind of recommended that you can use one of those on get the data into this. Stretch it. This one's already been done. Feel free to use this one for the products in this class or an editor edited version of this one. Now I've got these headers in their table already along the top there Home team homeschool away, Score away team. There will become important. So making sure I mentioned those and I have it displayed in weeks with a empty columns here for the scores Now, first thing only to do is highlight the range of cells, some starting from row free here because I want the home team away team etcetera to be recognized as the Header Road. So making sure that I still that those first I'll click on former as table on home, choose a style any style double check the range. Thank you. OK, And that is assigned and I wanna might do is just quickly go and change that style to the no style option because what I really want that's happened on this one. I have applied some formatting already in this example that I'll want to use same as before . I will turn off the filter button and give it a name, and I'm going to call it fixtures and we have a second table. Next up we have the calculations sheet. Let's do that one. So I've got some columns already set up here for us. Ah, well, as you can probably imagine by the headers, they're going to score data such as or store data story, such as gold scored. Number of wins, number of losses go different, etcetera. Now, before put this in the table, first of all, want to populate it with the names of the teams for this competition? And to do that, I'm going to lengthy sales to the team's sheet. So the 1st 1 cell see free I'm going to talk equals I am going to click on the team's sheet and select I X, the very first team name, which is in so a to off the team sheet by all presenter on that is set up a link between 02 sheets. So if those team names were to change, this calculation sheet would automatically change. I am going to copy that formula down so that you'd work for the other team names in my list . So I now have the 10 T names, and that will change in the future. If the team names do now, that's done time to set up the table, maybe highlight the ranging question a 22 K 12 and go through the same situation. I'm going to apply the style. I am going to turn off the filter button. I'm not going to give it a name on. It will be called Calculations on Only Go to the last example the league table. Now, before I put listen to a table I'm going to, first of all, into the positions off the table Until are you free? Oh, top number one percent confirmed. I'm going to copy that down to Row 12 because I have 10 teams that will duplicate the content Number one and I will click in this little tag that you get. I could feel Siri's just as a short cut. Why off generating numbers? One. Detect. There are other approaches to do that, including just typing them. There was only 10 numbers that would almost be is quick, but I guess no ever had free 100. Once that's done, I will select the range A to toe 12 for matters table up from the home tab. Any style. Remember, I'm aware I keep using the same one. Any style. Double check the range click. OK, that once again, I'm gonna turn off the field top. I think anyone's really going to do that. Use that I want to give it a name and that name is going to be league table. I've put underscoring between the names there because I don't like spaces in my names of things and press enter, confirm and we have our four set up tables ready to go. So in the next lesson, we can start right in their formulas to stop populating. But this calculations sheet 3. Calculate Games Played, Won and Lost: hello and welcome to this lesson where we start to calculate the league table data. Now This lesson is going to cover calculating the number of games a team has played. The number of games a team is one on a number of games a team has lost. And over the coming lessons, we will calculate other data such as games drawn, go scored, points, goal difference, etcetera. I wanted to break this over different lessons. Woman having one humongous video. But this lesson will be the biggest off those because I wanted to get some groundwork done on a function called some product. That is the function that I'm going to use. For the majority of these calculations. The calculations can be a little bit intense, and some product is an extremely powerful but also very flexible function. So by using this, my idea was at this same function concussion a majority of the calculations. So there are often and using lots of different ones. I can keep it consistent and uniform. Aziz Muchas, Why can't do it? Won't award of them. And yeah, we're hoping that will be to go west, the name behind it. So despite being a very powerful, flexible function. No, its not necessarily one that you may be used to is not massively mainstream. See, this may be the first time you've seen it, so yeah, we'll walk through quite quite gingerly to get a feel for it. Now, what have also done here is we have the fixture sheet. And remember in a previous lesson we created a table and we named it at fixtures. And I'm just showing that on my screen right now in the top left there, we named it fixtures. Now I've also inputted some results, so we will get some day to show up. As we do, the formulas will see the benefit, the fruits of their labor, back to calculation sheets, cell D free. Let's get this some product function up and running. Now ignore. These are great questions. We don't care about that right now. Feel free to check out information on some products. In fact, I encourage you to go and do that amazing function. But right now I just want to get this job done and we're going to enter each piece of criteria in parentheses, in brackets. That's what we're going today and to find out how many games a team is. One we need to first will find their name. So I look for I accident a home team section. If you see the name by X, then check that the home score section has a number in it. If there's a number, don't get their played again. Then looked any away team. If you see I acts, have a look at home score. If he's got a number there, played at the two of those together, that be how many games have played? That's your idea. So in goes at first bracket at first present disease for the first criteria, and I will reference the fixtures table that I just mentioned and it recovered in the previous lesson. Also opening square bracket. This helps us reference a column or field from that table, and I'm choosing the home team. She couldn't see their home team home score vs away score waiting. Gonna reference home team closing square bracket equals the team right there in my capitation table. Look down the home team column of fixtures. Can you see that team name in it all? They decide closing bracket. I'll repeat every piece of criteria we're going to put within brackets and you see them color red trying to help us notice that progress are now going to put in the asterisk the multiply sign in Excel formulas. Now you can think of that as and logic. If you're not familiar with and logic or logic, then please don't let this worry you too much. We can get through this and you know how it will make some sense. They can always explore him kind of topics outside of this class, but we're trying to say it's I action, a home gym section. And also there's a number in the score section of wise. If there's not a number, there's no there's no complaint this we're trying to say opening bracket is a piece of criteria. Fixtures table, open, square bracket. I want the home school column closing square bracket, not logic empty. So the less than sign followed by the greater than sign indicates not, and in a empty string, double quotes double inverted commas. Nothing in the middle indicates that there's nothing in there is a blank cell. I'm saying it's not a blank cell, so this is scoring that they will make you not blank closing bracket. Have you found the home team and the score sections? Not Blank. He's got something in it, plus sign. She can think of that as or logic so before the home section after that. Plus, I'm I'm now going to do a similar thing for the away section. So do it home do for way Adam together. That's how many total games played because he's this is for home. End away. I'm not section opening bracket fixtures table opening square bracket away team closing bracket equals that team name. Closed bracket for the criteria. Peaking at the speed a little bit here, but it's very similar or it's the same. Really. Is watching for home in every way. Multiply and logic. Opening bracket criteria is going to be within brackets. Fixtures table. Open square bracket away. School closing square bracket. Not empty closing bracket for that criteria. Clothes and bracket for the some product function, and you may see that go black to indicate that is the sum product function that's a matching bracket. There it's all over. The blue box disappears. Also, presidenta and copying down produces the results, and that's Mbuyane of play two games each. The rest of only played. The one still could've fixtures. There's buying and Anderlecht down there paying the extra game in the second week drop from running there already. So the results is accurate. We contest out of their sample data, a bit of a monster or formula. This may take some getting used to you for some of you that some of you know some of you may already be familiar with this would be up and running and hopefully as we go fruits. That is certainly not the last one we're gonna do at five more functions like that. And that was my goal. If it's consistent, you'll get a feel for it. That was one of hoping for Robin and lots of different for minutes. Okay, so in the wind section we get some practice straight away. You Right now let's get a some product function going open bracket. But first criteria Now for each one, we will be checking for home and away that it's the team that's always the first check home team is a team. I'm looking for Andi. So now I'm trying to calculate if it was a Game one opening bracket fixtures table. The home school is greater then Victors haven't square bracket their way. School closed bracket. For that criteria, that would be a home game one. You can see the home team on the home scores of greater number in your way. Score. That's a win for the home team, plus sign, so we can now do the same for away side opening bracket for the criteria fixtures table I'm now looking for away team is equal to that team name. Company I X is the first team multiply for the and logic opening bracket for a criteria fictions table away score greater than fixtures table home school closing bracket for criteria closing bracket for some product function and that I'm just checking it over. The looks good should bring me number of wins. I actually got a zero. Let's hope will get numbers for others. Here we go. That looks about right. This four teams there blind of one both Monaco 11 Let's have a look quickly. There's a win for Monaco there by and didn't win both for free to new. Yes, they did. Looks good now for the Los Ese I'm going to go into that win. I'm gonna take a copy of that formula. I'm gonna cheat a little bit here, press escape, going to los ese paste that in. It's gonna change around. I'm saying right home school Less than away Score a y school. Hey, we got less than home score, so if I do the same check, but check of its less than their now on finding out if it was a loss, that's the logic I x lost that game. Apparently, this could be it down. And here we go. Looks about Roy lost for aye, accident Elect that. Have a quick check yet bikes with a team lost to Monaco and elect lost it by in there. Her case. There's the first free columns in played wins and losses. I hope this some product doesn't feel too bad. I really don't want to put you off of it, is hopefully if you've enrolled in this class, you have an interest in calculating football or it may be just in general sports league tables and you can see the power behind it now And how, By using the table from a previous lesson, Maybe this is it doesn't seem too bad with those names involved. That was certainly my plan anyway. 4. Calculate Games Drawn, Goals For and Goals Against: hello and welcome to this lesson where we need to calculate how Maney Drawers team has had . And then their goal scored and goals conceded in this one video. This one lesson. So starting off with number of draws and this is the biggest formula off the table now, really, it's only a tiny extension on what we've been doing before, because this time we need to first of all, find the team name in the list. Then we need to check that the home score on away score equal in the counter drawer. But then we need to also check. This sale is no empty because in the fixtures list when I haven't played, that is a drawer. Right now, those two cells are equal to each other, but I need to make sure that you know there is EG scoring there. They have actually played that game is it is happening, okay, and we need to do that, obviously, for the home and them for the away. So in the drawer column. Let's get the some product function going, and hopefully you're beginning to get that little bit more comfortable with this functions . We go so once it's started opening bracket for the first condition. Each condition goes in brackets, and I'm going to check the fixtures table to check. The home team is equal to the home team in column. See there So fixtures home team in square brackets equal to the team written and see free. I clicked on it, it says at team, because when a tape closed bracket, there's the first condition in. Is that team in the home team section? Andi Opening bracket. For now, it's condition if it is in the fictions table. Home school. Close square bracket equal to away school. Close square bracket for the field. Close normal. Back you for the condition Our school is the same on and Samo plus symbol for end opening bracket fixtures. Home school. No empty closing bracket for the condition so they're not. Symbol is the less than symbol followed about a greater than symbol, nothing in the middle of it all, and then also to inverted comments to double inverted commas. Speech marks, if you will. Nothing in the middle is that the home team that equal and there was a score in there. Now we do the same for the away side of things So plus for or logic opening bracket for a condition. And if we don't fix his table opening square bracket away team close square bracket equals that team closed bracket for the condition. Andi Open bracket fixtures away School bqool toe pictures. Home school closing bracket on the condition. So easy to forget those on and opening bracket pictures. Table away. School must not bay empty lows back in that condition. Closed bracket on the some product function and you may just about tell that brackets black matching the one for some products. So I know I finished there. I could also see these little blue box disappear as I do that percent to and copy it down on. We have draws for and elect Celtic, Liverpool and Spartak Moscow. And if I check the fixtures table, there's the draw between and elect and Celtic. There's a draw between Spartak and Liverpool, and we seem to be working okay there. So now we're going to go scored and goes conceded which hopefully will be a little bit simpler because this one just requires us toe. Look for the team in the home team section on an ad up the goal scored, then look for them in the away team section, Add up the goals scored and and do the complete opposite university. So some product function is coming in first. Open bracket for the condition, which putting it in a bit quicker, if you don't mind, because it's not their first time. Now you watch me messed this up. Now it's just a check that that team is in there. Look in the home team section for that team and multiply it by in the pictures table, the home score section. And that is that for the home team? A lot simpler. Look for them in there. Add up what you see in the score area and then do the same for the other side. So if we go fixtures away, team is equal to the team that we're currently working with. Close bracket that condition. Easy thing to forget. I think Andi open bracket pictures were added up that away score. Now we are double checking myself. Close square bracket closed bracket close back again for some product changes color and we have some goals. Excess good. One goal. Good that six for buying Munich. Let's go and have a quick little check. It's time Just confirm got formed to That was good. I've got gold fry acts there. Yeah, seems seems OK now way school will be the same, but in reverse. Really? So some product function that will have fixtures table home team equals the home team. But this time we will be adding up the A Y school cause that now indicates that that's what they conceded. Find them in the home section, add up what you see in a way school. I could really just copied the previous formula and tweaked a little bit. Maybe in goes a plus sign. That's do the same for the other side groups. Mr Wrong Button. Finish off my pictures. So now we're looking down the away team section for that team, and we're adding up the home score sections. That's what they've conceded two brackets on the end because that video for that formula, if needed, and down we go for the gold proceeded. So now we're slowly getting there, populating the calculation sheet. We have games drawn. Gold scored goals, conceded data in our table 5. Calculate Points Earned and Goal Difference: Okay, now, in this lesson, we're going to look at calculating the points earned and then the goal difference as I have a football league table. Example now beginning with the Points column, and you may be glad to hear that we no longer need to some product function. We needed that for that slightly more intense, conditional calculations. Now it's just got a little bit simpler at this point column. We need Teoh start off formula by saying, equals I didn't click on the number of wins. So that will say at W, because we're in this table and I'm simply going to multiply that boy the value that I have only data tap. So in a data tab, Selby, too. I have entered how many points I would like to provide for win. As always, you can do a similar thing for your own table, where you may get a different number of points to what I have recorded there. I'm then going to press the F four key, the function key F four on my keyboard. To make that reference absolute. It will put those $2 signs in fixing that reference so that when I copy it on the league table. That be, too, will stay as B two if you're not too sure what I'm talking about. The moment by absolute reference in, then check out the getting started with formulas a class that are off from school share, as that would explain that as one of its lessons in a little bit more depth. What I'm going to worry about right now, you could just type those dollar signs in. No, the F four key is simply a short cut to get them in there. I will follow that with the plus sign, because now we need to also add the points they get for drawing. I'm going to pop back to the calculation sheet so that I may click on the draws cell and it will say at draw and once again at multiply that value by how many points you may be given for I draw, which happens to only be one here, but I'm going to click on that cell anyway. And just in case it would change in the future, maybe on I'll make that reference absolute on an old press might indicate, So I slightly more simpler formula. It's okay to say so. And then we go. We have a record of how many points I am unit getting a six there for the two wins and so on when we never Basel. And so okay, now won two gold difference. Another quite simple formula. If you don't mind me saying this one will be equals the number of goals scored at F and subtract the number against at I Asia. Is that president? Copy it down. We have a record off the goal difference for each team in their table. 6. Rank Teams by Points: hello and welcome to this lesson where we begin to rank the teams off league table and we're first of all going to rank them by the points earned. And then, in later lessons, Bean to add other criterium in to rank them uniquely. Okay, So in the Rank column, Selby free. We're going to use a function called Rank, a useful exhale function for this type of work. Can't use the normal rank function here. So opening bracket that's going to ask us free questions, the 1st 1 being the number that we wish to rank. And in this example, that will be the points that this team has earned otherwise known as at points. Come on. Second question is the reference references. So I was looking here, and I'm going to highlight them at the Whole Points column, known as points because of their table. So look for the points you're owned by Iraq's in the whole list off points coma. The final question asking you which order to rank them in. The default order is descending, which we could also specified by typing zero or weaken type one, indicating descending Now. I do want descendant because I wish to say that the higher the points Diana and is the lower the number, So I e the one with the most points. The highest number are actually number one to make sense. Those are the second highest points were actually number two. So it's in descending order now because that's the case to repeat myself, I could top zero to indicate that. But I could also just ignore the question because that is the default response. Anyhow, that's what I'm choosing to do. I'm gonna put my close bracket enter and I don't copy that down. And we have I rank for each team Now, in this example, we can see that we actually have some conflicts. I've got a few teams that have one point. I've got a couple of teams of three points, you know, we got some conflicts there, So in the next lesson, we're going to start looking at uniquely ranking these teams. But now we have a point ranking on the board 7. Uniquely Rank Teams: hello and welcome to this lesson on uniquely ranking the teams in the league table. Now when this league table is finished, we, for the most part won't need war about to do, because we will already have criteria such as gold difference goal scored, which will determine their teams league position. Let will probably be unique, however, at the beginning of a season when there's only one or two games in. There's going to be a lot of conflicts. And, you know, like we've gotten this table at the moment where large teams are not uniquely ranks until the season begins to get properly underway. So I'm for the most part not needed. But there's always a chance and definitely begin of the season. So let's start putting it in and it's all gonna happen in. So are you free? And then we'll copy it down for each team and we're going to begin by typing equals and clicking on the rank off. That first team on what I'm going to do is add on to that discount if function on account, if function if you're new to it, as its name suggests, simply counts the occurrences in a range if they make a certain condition. We have two questions. The range and the criteria were looking for. Now the range. I'm gonna have to type this in. It's probably easier and then to top dollar, sign the stole of sign free. So that's so Sief. So be free. Still be free, which is covering this form of moving into at the moment. But I've used dollar signs to fix that or making absolute. If you're not familiar with the county function or what I'm doing, what now? With dollar signs? Then check out my Excel formulas for beginners. Course, it's a free class available in skill share, so feel free to enrolled in that. And I have some proper lessons dedicated to both for those topics. For now, though, continuing with this range I need my colon is part of range. Then I'm going to type dollar, be free. So what I've got there is the first part of ranges fixed that will always be be free when a copy it down this table. But this one will not that be so yet free does not have a dollar sign before it. So when a copy down the formula? No. In the next row, that's gonna be be free to be four in a row. After that, you'll be be free to be five. So it's going to roll with the expand with the formula. Okay, comma, what's the criteria? But I'm gonna click in this cell down here that I'm able to because I want the one behind that formula about can't get to it. They're not just gonna use the arrows on the keyboard just to move up into the cell that I care about. Alternatively, I could have just taught that in, uh, opening square bracket at rank closing square bracket. So that is basically be free, but using the structured reference provided by the table as at rank Now, at that point, I'm gonna put my close bracket for count. If I'm going to put minus one and I'm gonna press enter to see the result, copy it down. And it might be easier for me to explain what I've just done. That several presenter copy the formula down and it now unique to ranks them. And how that works is it looks at this rank. Remember, This is counting said at the time it gets to Barcelona, the third team, it realizes that number eight has already been mentioned, so there's two bites. So what it does here looking at a function is it looks at the rank, right? Adds on top. How many times, too. But it takes away one. So ends up adding one on top of the current rank so that I ate here becomes annoying. And here notices free off them. Take away one's who adds on to so that it becomes 10. And that's how it works. Hopefully, that made a bit of sense. It might make me that re watching this video, maybe, or looking through that formula to a three times if you're not too sure. But that does the job of uniquely ranking these teams if there's no of a criteria that's capable off doing it, so at least they get a unique position within the league table. 8. Rank Teams by Goal Difference then Goals Scored: hello and welcome to this lesson where we look at add in extra criteria to determine the ranking off the teams in this league table. So we already have the ranked by the points that have earned. And if they have to say, number of points rank them uniquely. But war would like is if they have the same number of points I want it to then check the goal difference and rank them by the better goal difference. But if they have the same number of points on a single difference and then I wanted to check the ghost scored on ranking by that. And if you still can't get a unique Rankin after checking their points, earned the goal difference and the ghost gored, then just uniquely ranking by what we haven't caught on a. So that is the plan on what we're going to do is add some extra columns to this table in columns l m and n on the one in column L, which I'm gonna begin move is actually just going to duplicate what's already done in column B. That might seem a little bit silly, but hopefully why I'm doing that will become clear as we progress. So just entered a nice head off for a table there and automatically expense, which is some of the benefits of using tables on why we did what we did in the very first lesson. I'm just going to as a sigh, repeat that function. So I'm gonna use this rank function, and I'm gonna rank it by the number of points in the list of points. Eso that is literally the same as what I've got in column B I could even have just copied would have done in column B. I'll never put that in and copy it down. Here we go is exactly the same as what, Gabi. But column B will soon change. Believe you. Me? Okay, so that's that one done. I've got a ranking by points. Don't call him in. I'm going to have a ranking by goal difference. I'm gonna call it rank. G day is in us. Head up, and we're going to bring in the some product function again. You go. And what I'm going to do is first of all, give it a criteria to check that the number of points is the same. So in a reckless in brackets like the previous lessons. So in goes my open bracket there on on it to look away the points and check if they're equal to the points that that first team have got so that any other teams in their that the same number of points is that team and I can see that there are. There's to Barcelona and Juventus. The also have the same points of Iraq's closing bracket. For that criteria multiply which remember, is there kind of and logic opening bracket for the next criteria And I now want to check in the Gold Difference column. If anyone else has got a better goal difference, a greater goal difference, then that teams go difference closing bracket for that criteria closing bracket for the some product function and Oprah Center and as a co pay that down. However, this begins to make sense, as we can now see as we look down that you Aventis have a number two next to them and I was just trying to say that when you look at this, they've got the same number of points is Iraq's in Barcelona, then you look at goal difference and There were two other teams that have got a better goal difference than you've interested, and that's what is trying to say. Where's the Barcelona? It's got zero because they're actually level on goal difference as well. So you got the same number of points on the same goal difference. However, both runners scored more goals, so that brings us nicely on start to rank by and next criteria. Say, yeah, rank goals scored my talking there. But then and I'm going to do a some product function similar to the previous one. Let me move this especially along. So the 1st 1 is to check that the points of the same. So look, order points are the equal to that team's points. Close bracket multiply next open bracket. Look all the goal difference. I only eat cortex. Team's goal difference, like is the case for Iraq's in Barcelona multiply next criteria. Look algo scored is a known. Got a greater goal scored than that teams goes. God slows bracket for criteria. Close bracket some product and it goes. And yes, it's safer. I ax now. Yes, there is one extra team that's got same points and go difference, but betters go scored. And that team name would be Barcelona. Here we go. So now we've got these extra columns in I'm not resize that one. Doesn't need to be that what? Water and to make this work. Now, we're now going back to the rank column that ahead earlier on ranking by points. But now another call. I'm doing that. I'm gonna delete that stuff in there. I'm gonna replace it with a simple formula that's just going to add together. Each of these, someone say, equals frank points plus the rank by goal difference. Plus the ranked by goal scored. I confirmed the entry of Enter on. Copy it down. Ah, now that's starting to do some unique rankings based on that. So when you look at the free teams to keep picking on it now knows that Barcelona a team eight by action night position and you dentistry in 10. Because far, for example, Aventis, It kind of found the ranked by points eight but in the editor to one because it knew there were two teams in a better position and I ax Renton by points. But an added one on because there was one team in a better position. Donald Tax. This is Barcelona on Barcelona. It left alone, right? Ah, there's no more energy to your better goal difference. And that's how I was working. I'm really hoping that, like my explanation is, is making some sense. Now. We still need their column A and A sitting in there, pretty doing its thing. And that's important because Liverpool and Spar attack have not been uniquely ranked, and that is because they played each other. They played the child but have only played one game. It was 10 I can't separate them in any weight off them right now. So I just got this unique ranking, making sure that doesn't affect Ellie table. I'm sure in one or two games time there will naturally be set, prayed by ported by how many points have earned. But if no go different to go scored. But if there was free things foul to separate them, then just create a unit ranking for to me right now on now we've got a league table going. This is our calculation sheet. So the next lesson he's to create a league table because now we have our rankings 9. Populating the League Table: hello and welcome to this lesson where it is now. Time to populate our league table. All of the lessons up to now have been setting up the calculation shapes, setting up the ranking. Now it's time for the finished product. And what we're going to do is use the V lockup function in all of these cells of the league table to look up and return the relevant data for a table that was done. The Rankins and I've got my positions, 1 to 10 down here, which I'm going to use I'm going to ask for you look up to look for that position inside the calculations table and return the relevant information. They're gonna look down the ranking here, find Number one return information from the third column, but Serb a minute for team name. I am for the league table. Also column 456789 and 10 for points. That's what we're going to do. So on the league table sheet Selby free the first V look up function. They look up. Value will be the position. Look for that rank in that position. Lips comma. The table array is the calculations table the type in it will come up just like the fictions table and all the previous formulas. Comer. What column has got information You want to return? Wolf? A team name? It was column three coma. Is this like a range look up or Ah, precise. Look up is precise. I'm looking for a specific position. So tight, false to say exact closed bracket. And in tough I'm UNIX number one. Copy it down and we've got the relevant positions away. Fruit. There's the Barcelona. I actually Venters have been talk about a lot of my lessons. It's my sample. There's little point Spartak separated. I don't recover the others. What? I'm going to do it. Just dip into that formula. Copy that V. Look up, Come back out, go into the next cell, paste it in and change a column index from free to four and just keep going along the top. There are some clever ways that we can do this kind of stuff to save me, doing what I'm doing now. But because I'm probably never going to change this, you know, it's just a few seconds of effort. I think I'll just do it this way if I can get my column numbers right, that is. Come on. Number seven right now. Number eight. Now number nine. And finally number 10 for points placed in Changed a column. Index points once they're old in sure to highlight that and copy it down for the others. We've got a league table, Hope United, and that is that for Take it my calculations. All the data is coming from here to come free. 45678 9 10 For anyone who may be new to be, look up, that might take a bit of getting used to as well, really useful function to know if you're no even aside from we're doing right now, but that is a football league table created. 10. What to do Next: Hello and welcome to the last lesson off the class where I wanted to start by thanking you for checking out my class. I hope you found enjoyable. I hope you found it useful and have learned some new excel techniques and skills. Now, the next thing I want to ask you to do is to tackle the class project. You may have followed me through this class taking each step as I was doing it. But then the next logical step is to cry and create your own league table. You can choose whatever type of league table you want. We're a B from the sporting world or from the business world, individuals or teams on whatever criteria you want. In fact, you're probably encouraged to try something different just to really put it to the test and set up your own league table. And then if you can share that with everybody else in the class, that would be fantastic. With the community. I'd like you to ask you to provide feedback. Never. You have enjoyed this class and found it beneficial. Please click the thumbs up. It helped me put the word out there so that other people can also enjoy the class like you have, and then it's just looking for opportunities to practice and to learn more. 40. There are loads of classes on skill share that you can roll with, and hopefully we'll get to learn mawr Exhale skills. There's also loads of blog's. There's load to stuff on YouTube. It's just putting yourself out there, too, to learn it off. His skill share is fantastic for a structured environment, which you may not necessarily find by blocks on YouTube, but they will be packed full of information on If you have especially enjoyed myself, which I hope you have. They improved to feed to check me out at computer guard. Guard that come. You can also find me on YouTube. You can find me on Twitter on Facebook. Please feel free to connect with me. Please feel free to speak to me and ask me questions and check out whatever latest tips I've got going. Why so thank you one more time effort involving and enjoying my class and hopefully speak too soon