SOLVED

Formula Help

Copper Contributor

I'm looking for help with these to spread sheets.  In the first picture I'm wanting the two tables to know when a team has won the division by the number 1 changing to a C for champions and if teams are in the relegation zone in the first division and can't finish third bottom then number changes to an R and if the second place team can't be caught from third place but can't win the league the number 2 changes to a P for promoted.

 

In the second picture I'm wanting the rankings table to tell me when A player can't not finish lower than 8th position meaning that that player has qualified for the end of season Masters competition.

 

If anyone can help me I would be most grateful.

 

Mark_Smith1985_0-1592314483833.png

Mark_Smith1985_1-1592314519171.png

 

22 Replies

@Mark_Smith1985 

if you attach copies of the spreadsheets then we can look - rather than pictures which makes it difficult to work with.

 

thanks

 

peter

Sorry Peter

I've attached it now for you.

Kind Regards

Mark

@Mark_Smith1985 

Mark, 

Questions:

1.  How many games will each team play in a season. I notice the first time has played 16 games so far.

2. How are points calculated?  I understand that there are points for home win etc. But there seems to be points per frame as well - which are variable? 

In order to calculate who the Champions are we need to know the max points the second team can get and if they can reach the points the first team have. In this example, first has 187 and second has 158. How many more games have they got? And can we forecast the frames points?  I guess not - and the frames total is much bigger than the point - so will make it difficult to forecast.

 

If there were no points for frames it would be easier to forecast - though we can see that the max score for frames is 12.

 

Let me know how many games in each league - is it 22?

 

Not looked at the player question yet.

 

let me know.

 

thanks,

 

Peter

 

 

@peteryac60 

 

Hi Pete

 

Answers to your questions

 

1. The two divisions are set up as 12 team divisions.  They are all play each other twice although we only have 11 in each division so there is a two byes for each team.  Each team will in turn play 20 matches but if the division was full then it would be 22 matches.  Each match is made up of 12 single frames

 

2.  Yes it's 3 points for winning the match and it's 1 point for a draw.  You also get 1 point for each from won.  So for example if the match ends "Home Team" 7-5 "Away Team" it would be 10 points for the "Home Team", 5 points for the "Away Team.  The maximum number of points a team can win in a match is 15 points.

 

I hope this helps Peter.

 

Kind Regards

 

Mark

 

 

@Mark_Smith1985 

 

I have done a little mock up with the first two teams. It looks very much like Harewood have won but it is not certain. The max number number of points the second team could get is 105 - assuming they win their last 7 matches 12-0 - which i am guessing is unlikely. I have a simple formula in the 1 box which will change to C once the points the first team has is greater than the points the second team plus the max possible points. If you adopt this approach you are probably not going to get 1 to change to C until there only 1 or 2 games left. As a test - if you change the 7 games left to 1 you will see it work.

 

Have a look and see if this is what you want. it if it the R can change as well but with the same logic in reverse.

 

let me know.

 

Peter

@peteryac60

 

Hi Peter that looks good i think i'm getting my head around.  I'll add it to the spreadsheet now.  how do you mean for R it would be in reverse how would you write that?  also in division 2 how would you turn second place to a P.  Thank you for takijg the time to help me Peter

 

Kind Regards

 

 

Mark

 

  

When I put the formula in A3 it comes up with a blue line across the cells

@Mark_Smith1985 

 

For Relegation - if the bottom team cannot catch the second bottom team (using the same logic e.g. number of games etc) then you can change the bottom team to R.

For Promotion in the second table , you again use the same logic but instead of changing to C you change to P. Does that make sense?

 

HOWEVER - I have just found out that the ranking numbers e.g. 1,2, 3 are used in the formula in the team names - so you can't adjust the numbers to C, P , R or anything else!! If you manully change the 1 to C you will see what happens!!

 

You may need to think of a different way to do this - did you inherit the spreadsheet form someone else?

 

Peter

 

@peteryac60 

Hi Peter

 

Hope you are safe and well,

 

Thank you for sorting that out for me on Tuesday I didn't have my laptop yesterday so sorry for not replying sooner.

 

I was wondering if you can help me with the second part of the question from yesterday in regards to the Rankings system.

 

I want the Rankings for both Division One and Division Two to tell me when a player has qualified for the end of season Masters Finals when then have secured a top 8 finish.  I was hoping that when a player can't drop out of the top 8 the row with that players record on will turn yellow and in the box I have highlighted yellow  a "Q" will appear.  If you was able to show me for the division 1 players I would do it for the division 2 players.

Each Player can only play 2 frames in each match so this season that's a maximum of 40 frames they can play.

I have attached an updated file so you an see what I mean.

 

Hope you can help me.

 

 

Kind Regards 

 

Mark

@Mark_Smith1985 

 

Hi Mark,

 

This is quite tricky - not least because of the scoring system.

I have attached a working sheet which highlights the difficulties. The sheet shows the current scores, the maximum a player can get (ignoring the fractional calculations) and the max possible ranking.

Because they are so many variables you are not going to know until 2-3 games left which are likely to be in the top 8. In fact you might not know for certain until the last game is played. This is not unlike Premier Football League when we often don't know which top 4 teams will qualify for the Champions League till last game of the season!

 

Sorry - maybe not the answer you were looking for. Let me know if I can help with anything else.

 

Peter

 

@peteryac60 

 

Hi Peter 

 

I think from what you showed me with the league tables I may have worked it out for the rankings.  Like you say it will be hard to work until a few frames to go.

 

 I have attached it for you to see if you think I could be right in what I have done.  If I am correct then I'm amazed then all I need to know then is when the qualified sign comes up how does it change that players record to yellow?

 

 

Kind Regards

 

Mark 

@peteryac60 

 

Hi Peter

 

There is just one final issue I'm having which I thought would be easy but somehow I'm not being able to get my head around it.

 

You'll see in the tab "Player By Team Rankings" (Below) I'm wanting to sort the players rankings by team so they can see how they are doing within there own team.  Now I thought it would be a VLOOKUP formula but somehow i'm not getting it to work.  I managed to work out how to do the Players Rankings within the whole Division as it was using the RANK from the end columns R & AI within the "Collation" Tab.  I don't know if I should be pulling the data from the "RANKINGS" tab or the "Collation" to complete the "Player By Team Rankings"

 

Please can you show me the formulas for the first team BLUE BELL WOOD A on how to complete a rankings table by team and I'll have a look at the formulas and how you done it and I'll do the rest of the teams.  The rankings go off  Most Wins then the Win % if that helps ?

 

As you can probably tell I'm bit of a stato man lol.  Which I am but I also want to build the perfect league file for each season I don't have to enter everything manually lol.

 

Thank you for spending time helping me Peter.

 

Kind Regards

 

Mark

@Mark_Smith1985 

Hi Mark,

For the first 2 guys in Div 1 you have added 40 points to their total.

For example, Sean had won 28 games , lost 4 and had 8 more to play. So his current total (I thought) was

28 points and if he won his remaining 8 he would have a maximum of 36? Is that not correct? Or I have missed something?

 

Which cells do you want to change to yellow once a player has qualified? Just the qualified column or the whole row?

 

I'll look at your other comment now.

 

thanks

 

Peter

@Mark_Smith1985 

Hi Mark,

The VLOOKUP function is not much use for what you are trying to do.

What version of Excel do you have? I understand that in Office 365 there are a number of new functions (e.g. Filter, Sort, Sortby) that will allow you to manipulate the Collation sheet to get what you want. Unfortunately my version of excel is older so I do not have access to the new functions.

However, I have managed to achieve the same results using a Pivot Table. I don't know if you have used these before but they are relatively straightforward.

 

I have added 2 sheets to the workbook - both with Orange tabs. 

The first sheet is  a PlayersResults table. I copied the data from the Collation sheet to this sheet and converted in to an Excel Table. You can actually change the data in the Collation sheet into an  Excel Table by placing the cursor in the table and hitting CTRL+T or from the Home menu select the FORMAT AS TABLE. It is not absolutely necessary to use a table - if you wish you can leave it as it is but I would recommend that you use a table.

The second sheet is the pivot table which shows the rankings. You can select the team you are interested in at the top and I think this will show you want you need.

You can create your own Pivot Table using the data on the Collation sheet (as a Table or not) using the one I did as a sample.

 

I hope this helps!

 

I would challenge you to a game of pool but I don't think I would have a hope in hell of beating you!!

 

Regards,

 

Peter

 

 

 

 

 

 

 

 

 

 

@peteryac60 

 

Good Morning Peter 

 

I'll have a look now, yes it is 365 that I use.  My friend did something for me using a pivot table but it looked a bit to technical for me lol.  

 

I'm a very average player lol

 

Kind Regards

 

Mark

@Mark_Smith1985 

Mark,

 

I think I have found a way to identify people in the top 8. see attached.

basically, I check the CURRENT score of people and RANK it against people MAX score from position 9 downwards. If the ranking is 1-8 then this means that a person with the CURRENT score has more points than the maximum possible that a user below 9 can ever achieve. See the attached - I hope that explains it for you!

 

all the best.

 

Peter.

@peteryac60 

 

Hiya Peter

Thank you for doing that I will have a look at it and get my head around it.

 

Kind Regards

 

Mark

@Mark_Smith1985 

Hi Mark,

 

I noticed there as an error in the file I sent you so I have updated it for you.

 

BTW - where is your  tournament run? I assume you are in the UK somewhere?

 

all the best.

 

Peter

Hi Peter

No worries ill take a look at the new one when I get into work. I'm finding it hard to understand how pivot tables work :see_no_evil_monkey:

Yes we are in the UK we at situated on the boarder of Manchester and Derbyshire. Just by the peak district.

Kind Regards

Mark
1 best response

Accepted Solutions
best response confirmed by Mark_Smith1985 (Copper Contributor)
Solution

@Mark_Smith1985 

Hi Mark,

 

There should be a blue box "Mark as best response" at the bottom - just press on that.

 

thanks!

 

Peter

View solution in original post