Create a League Table in an Excel Spreadsheet - Part 2 of 3

[@Smonster42]

I wonder if you guys are still on this channel but here goes: I'm trying to rank colleagues by their performance, but whenever someone scores 0 (plus the offset technique when people score the same) they get put in the top rank of the ascending order. Can I get Excel to disregard scores outside of a set range? These vids and worksheets have been a massive help in learning excel. Many Thanks, Sam

[@TigerSpreadsheetSolutions]

Hi Sam Great to hear that the videos are helping. And yes, we do monitor this channel, almost on a daily basis (!) I like the idea of disregarding certain numbers; but, as far as I can see, the various Rank formulae do not allow us to do this. The below approach works on the same principle, however, by 'cleansing' the data before applying the Rank formula. The first approach I would try would be to add an additional column next to the column that contains the scores. In this column, use an if formula to display the colleague score if it is not zero, and display 100000 if the colleague score is zero. It would look something like: =if(A1=0,10000,A1) This will give you another column showing colleague scores, but with the zeros turned into very large numbers. Then, point your rank formula to this column. Let me know how you get on and feel free to get in touch via the website if you'd like to start and email dialogue.

[@TigerSpreadsheetSolutions]

Ps. Also check out the next video in the series, which is about dealing with duplicate values Create a League Table in an Excel Spreadsheet - Part 3 of 3

[@haimonagardiner]

I have been searching for this for a decade (obviously not very well since it took me 6 years to find this one lol)

[@TigerSpreadsheetSolutions]

Happy you have found us! I hope the video helps.

[@dalemallon1108]

You didn't explain that if the rank is the same (like the both students who ranked 1 in the first video) the match formula wont work fully until they are differentiated and that a duplicate rank would appear as N/A... Other than that the video is very insightful

[@TigerSpreadsheetSolutions]

+Dale Mallon Hi Dale - thanks for the comment. As you mention on your comment on the other video, the next video deals with this.

[@goldenengg]

Thx a lot

[@TigerSpreadsheetSolutions]

Thank you and welcome to the channel

[@ThomasApostolidislogistis24]

Hello! Your videos are very useful but I would like to ask something. from a league ( let’s say with 15 teams) 2 of them have the same pts but team A have +3 difference and team B -2 difference. According to your example team A will be on the top and that would be correct if 1st criteria is goal difference. But i want as first criteria to be the match between them. if we assume team B (with the -2 GD) won over team A (+3GD) how can this be shown in the league table.

[@TigerSpreadsheetSolutions]

Hi there - good question! One approach would be to include a result matrix, a table to show each team's result against all of the other teams, somewhere in the file. Then use VLOOKUP or MATCH / OFFSET or XLOOKUP to look up the result between the two teams that are on equal points. Use the outcome to drive an increment mechanism like the one shown in this video series. Not easily done but entirely possible - good luck!

[@ahmad.s4723]

Can you please make a video on project planner in excel with gantt chart in it? Thanks

[@TigerSpreadsheetSolutions]

+Solomon Kane Hi Solomon - that sounds like a good idea for a video sometime this year. Leave it with me!

[@ahmad.s4723]

great, i am looking forward to learn from it..thanks

[@mrliamwetherall]

If you're looking for teams to be ranked based on goal difference would you put less than or greater than????? plz help me I'm only 14 and am getting new to this

[@mrliamwetherall]

Hi I really love this and how you're trying to help but I'm so confused why you wouldn't just do a football one as you mention league all the time you wouldn't really associate that with students!

[@TigerSpreadsheetSolutions]

Hi there - this was inspired by a real-world project where I was asked to do exactly this - rank students in a league table! Check out our other league table videos for a recent football example.

[@mrliamwetherall]

Tiger Spreadsheet Solutions i did ive looked through all the videos except for 6/6 coz thats when i need to go to video to know how to rank and offset etc and im just wondering in my league ive only done 2 weeks worth of matches but do i have to do a whole season. If there isnt a way i dont have to do that itd be awesome if there is any other help because i get different ranked teams with the same number and then my offset everything is just shown in Alphabetical order and some teams even show up twice. Also the match format as well goes the same for rankings I know that was a lot to read sorry but yeah this channel is awesome is this a proper business people can work for???

[@ttapper1]

Can I jujst download the finished table so I can change the values for my purposes?

[@TigerSpreadsheetSolutions]

Hi Tommy Thanks for comment - the fourth sheet of the practice file shows a completed example that you can customise for what you are trying to do fairly easily. Get in touch via the website if you need a hand. tigerspreadsheetsolutions.co.uk/contact-us/

[@jonasjacobs6891]

when 2 teams draw in my league table, 1 of the 2 teams says “#N/B” how can i fix this and make sure both team names are in the table with the same amount of points?

[@jonasjacobs6891]

i think its #N/A in english

[@TigerSpreadsheetSolutions]

I think we deal with this in part 3 ...

[@dustindubiel9389]

What if 2 students have the same grade?

[@dustindubiel9389]

Oops nevermind, just watched the next one

[@TigerSpreadsheetSolutions]

Dustin Dubiel Get in touch if you need a hand Dustin tigerspreadsheetsolutions.co.uk/contact-us/