Forums

Excel help required for league tables & fixtures

• oneeyedjohn 9,588 Posts

  Tue, May 24 2016 4:01 PM

  This is the link I am using to set things up, but after entering the results for Week 1 I can only get a few results in Week 2 before the league table goes all screwy, with some players showing more than once.

  I have a list of 19 players and can generate a fixture list happily enough, but the league table is not working.

  If something ( computational formula ) is missing, then surely the comments on his blog would say so.

  I have tried everything I can think of to no avail.

  

   

  What am I doing wrong ?

  Desperate John

• andyson 6,415 Posts

  Tue, May 24 2016 5:54 PM

  After reading in the link provided I see you expanded the original from 10 to 20 teams.  There was a comment lower down on the linked page:

  MSOfficeTips:

  PUPPPU23 April 2014 at 14:13

  I am trying to further expand your model to be used with 16 teams but am unable to have the league table work. The reason for this is, I think, you have hidden a formula/defined name "Premier Calculations". It only works with 10 teams.
  
  Would you be prepared to share how this can be expanded?
  
  Thank you

  Reply

  Replies

  

  Alan Murray23 April 2014 at 21:39

  Yes "Premier Calculations" is a table which can be found on a hidden sheet called Calculations. You can unhide this sheets, they are only hidden to prevent accidents and confusion on them.
  
  Add the additional teams to the "Teams" sheet and then copy down the league table and calculations tables.

  Reply

  Did you unhide the Calculations sheet and expand it too?

• oneeyedjohn 9,588 Posts

  Tue, May 24 2016 6:22 PM

  I did.

  When I unhid it,  it only had 10 names in it so I typed in the remaining 9 names.

  Is there more I need to do ?

• andyson 6,415 Posts

  Tue, May 24 2016 6:28 PM

  only 9?  There's 20 on your image, so add one more?

  My other thought is to send your version to the original author and have him take a look.

• oneeyedjohn 9,588 Posts

  Tue, May 24 2016 7:41 PM

  I downloaded this twice from 2 different links

  First link  this is the one that the image above is from.

  The league table is good for 1 weeks fixtures , but screws up on week 2

  Second link this is the one ( blue background ) where i had to open the hidden calculations sheet and insert the extra 9 names

  This one screws up with spurious results being inserted.

  This image shows week 1 results only, followed by the league table it generated

   

  

   

  

   

  I notice even this has errors, with moio listed 3 times, Bboyce 2 times, and 2 players - YankeeJim & tygerjr missing completely, so something is fundamentally wrong, and given there is little one has to do to set it up, what can it be.

• andyson 6,415 Posts

  Tue, May 24 2016 8:28 PM

  I notice the Calculations sheet isn't visible above in the lower tabs.  You did unhide it and copy down the rows for the extra players, didn't you?

  **edit** another thought, perhaps the assumption in the original spreadsheet was all teams play every week.  What if you changed your fixtures to 10 matches each week?

• oneeyedjohn 9,588 Posts

  Tue, May 24 2016 8:41 PM

  The two links I gave above gave me two different league table generators, one with a blue background that was set up for 10 teams only, thereby requiring me to change the calculations sheet. This version shown below does not need to be amended, it will handle 64 teams.

  I did try changing the number of teams to 20 ( i added 'ANOther' ), but it already states it can handle an odd or even number of teams

  r-click view in new tab

  

   

  I have sent screenshots to Alan Murray, lets hope he replies, or even better, sends me the latest all singing 'n dancing version.

• oneeyedjohn 9,588 Posts

  Wed, May 25 2016 12:37 AM

  I have a reply from Alan already.

  He asked for the workbook so he can look at it for me.

   

  Thank you too Andy for ur help.

  My logical mind some times is too logical, it needs some lateral movement  from time to time.

  1i

• ScottHope 10,611 Posts

  Wed, May 25 2016 12:46 AM

  John, in Mr Murrays program, the data is set up in Tables (notice the drop down arrows in the header row), but when I look at the pictures you have posted, the data is not in a table format, just a normal data set. Maybe that has affected the formulas.

• oneeyedjohn 9,588 Posts

  Wed, May 25 2016 1:43 AM

  So have I corrupted the formulae somehow.

  Maybe I deleted  a line in the table, not sure, but I thought i restarted from fresh when I first encountered the problem.

  Alan will sort it for sure, he created it.

  I am just so delighted he has responded in double quick time.