Thanks Charlie,
The problem with moving my data across to your sheet is, I originally supplied an 'example', not a wip or proposal (I didn't think anyone would be so kind as to work on it). So now I have to figure out if I can merge your file and my actual working (IP) file.
I'm going to have another go, before giving up.
J.
Hi Charlie & all,
Charlie, I did as you said and moved my data across to your file.
First I seemed to get everything working for 4 teams, but for any more teams I would get, can't remember exactly but something like... !VALUE$, for consecutive dates and only a maximum of two of those errors if I entered 20 teams.
So then I changed the 'Weeks' & 'Games/Weeks' where you indicated in Sheet5. Now I seem to have only the first date (but none of the above errors), but I also have too many matches for the given nuber of teams.
It's obviously something I'm doing wrong, but I've tried several times and the same happens. My big problem and in honesty, is I'm too much of a newbie/dummy to understand your clever equations which stops me in my tracks for trying to find the problem myself!
I'm also a bit worried because it seems (perhaps I'm wrong) that the equation is going to be locked into a 7 day match play cycle (see previous post), I hate all the effort being wasted if it is.
I also don't know if the equation will let me do away with all of the odd fixtures in the list?. They were only a duplicate of the evens for my original example (in case anyone said... what about 5 or 7 teams?). I should be able to get the correct results by using "Bye" as my last team name where there are an odd number of teams.
PLEASE, tell me, if you can, how to change things or where I'm going wrong, it sincerely wasn't my intention for people to do it for me.
Thanks again,
J.
[EDIT]added attachment... latest file in case it helps[/E]
PS: Thanks to the Forum organisers/moderators for the Forum and use of it.
Last edited by Jo-Jo; 11-16-2009 at 01:23 PM.
Hi J,
Yes, I can explain the formulae, they are not too hard really, but this will take me a little time (and I do have a day job). I'll get back to you in a bit, this is just to let you know I'm not ignoring you.
CC
PS - to be honest, we have a habit of simply solving things because it's often easier than explaining, so sorry about that. It absolutely was my original intention to solve not to explain - in fact, most people don't particularly want an explanation.
Last edited by Cheeky Charlie; 11-16-2009 at 06:18 PM. Reason: explanation of lack of explanation
Charlie,
Please no rush, just whenever you get time. I've been working on this for weeks (with manually sorting the fixture (didn't know if there was an easy 'spreadsheet' way)) and have moved on greatly with your help over the last couple of days.
I can see it being easier to solve than explain to people, I just like to learn a little if/when I can.
Without solving and if you get a chance, is it possible to say from my last upload if I messed something up in your formula?
Many thanks,
J.
Hi JoJo,
Just checking - this does do what you need right?
We are on the same page with byes for odd team numbers - that is why when you say 5 teams it makes space for 6 - just put team 6 name to "Bye" and you're away.
On to the formulae:
Clearly there are no formulae on Sheet5 - just two tables
Number of weeks - this is referenced from the teams_weeks_games per week table with a vlookup - we use it to extend the lookup reference as far as the number of rows we need for all the matches.
Games per week - same as above, same reason, but we also need this to tell us how far apart to put the dates (column f) - you need a sufficient number of rows between each week to fit the right number of matches in.
Team number references (column B) - this is just a simple formula that rounds the number of teams up to the nearest even number, then displays each number - it references row() so as I copy it down, the number is one more each row... clever.
Team names you add yourself, I have just added a little conditional formatting to highlight "missing" and "not used" team names.
The dates:
=IF(AND(MOD(ROW(1:1),games)=0,ROW(1:1)<weeks*games),OFFSET(F19,-games,0)+7,"")
first:
MOD(ROW(1:1),games)=0
Divide an increasing number by the number of games per week - if it divides perfectly, the increasing number is a multiple of the number of games per week - so we want to display a date on this row (if two games per week, every other row needs to be a date, for example)
ROW(1:1)<weeks*games
The total size of the table (again measured with an increasing ROW() reference) must be equal to the number of weeks played * the number of games per week (obviously?) - so don't show us anything after that.
So:
AND(MOD(ROW(1:1),games)=0,ROW(1:1)<weeks*games)
Sets the row-density of the dates and limits the table to the total number of games (games per week * weeks)
then all we need to do is, if it is a "row with a date":
OFFSET(F19,-games,0)+7
add 7 (days) to the last date - which will be the number of games per week above the cell (F19 refers F19 i.e. itself)
To adjust this (holidays etc.) I would simply recommend adding a column for "revised date" - accomodating holidays is easy enough, but adding 8 days a subsequent week because you moved it backwards for another... no thanks
Finally - the big one (although not too long - good formulae don't need to be long!)
=IF(ROW(2:2)<=weeks*games,VLOOKUP(INDEX(matches,ROW(4:4),2+(teams-4)*4),TeamNames,2,0),"")
=IF(ROW(2:2)<=weeks*games,<stuff>,"")
only do anything if within the size of the table (like the dates one)
<stuff>:
VLOOKUP(<team number>,TeamNames,2,0)
When we find the team number look it up in the team names table (B18:C37)
<team number>:
INDEX(matches,ROW(4:4),2+(teams-4)*4)
index says look in a table (matches) pick out row y and column x -
matches is the big table
row(4:4) is the same thing we've seen before, it just starts at row 4 because your team references start on row 4 of the big table - as the formulae copy down, they each look at the next row down
for the column reference, we know the table has 4 columns for every team (starting at 4) so we need to multiply the number of teams by 4 to get the "start point" for the row, then we just need a reference for which column within the four - the first team is the second column of the set of four, the second team is the third column - that is the only difference between the two formulae.
I think that's it, get back to me with more questions
CC
Hi Charlie,
I'm not sure how to answer that Charlie... I have two problems, but I don't know if I created one of them!:-“Just checking - this does do what you need right?”
1.after I (so it could be something I've done) added the extra data as per your instructions, I get errors. These errors seem to occur when the 'number of teams' are changed and consist of some 'dates' not showing at all, some dates showing something like $VALUE! And also too many team names being listed
2.It needs to have the facility to exclude (or manually adjust without destroying the formula) dates if they fall on a bank holiday or such (the league runs over xmas etc when there are no matches)
To try to explain... when I originally uploaded my example, it was only that an example, which I didn't expect anyone would be kind enough to adjust (but many thanks). Anyway, on Sheet5 the lists are all duplicated in pairs, eg: '5 Teams' is exactly the same as '6 Teams', '7 Teams' is the same as '8 Teams', '9 Teams' the same as 10 etc etc etc. This duplication was only done for the example. What I planned to do was remove all of the odd team lists in Sheet5. This would mean that if we have a year where there are an odd number of teams, I would use/reference the even lists for the calculations/formula, but I would name the last odd team “Bye”. This would mean that one team would have 'Bye' against it (in the final play list it would show eg: Team 16 plays Bye). So in effect, the odd listings in Sheet5 could realistically be redundant/removed. Does that make sense?
“We are on the same page with byes for odd team numbers - that is why when you say 5 teams it makes space for 6 - just put team 6 name to "Bye" and you're away.”
Those are the only things I can think of. All I think I can ask is if you think I have created the first problem and if you think I can adjust your formula for the other things.
I really do thank you for taking the time to explain, I am going off to study... another late night/morn
Thanks again Charlie,
J,
Good morning Charlie & all,
As I was working through your explaination I saw your mention of the dates and the difficulty, so apologies for mentioning that again. I think I might have an option, but at the moment I am just trying to break the formula down by creating non nested formulas that do the individual functions... just so that I can learn (not to actually use in the spreadsheet).
Must admit, you guys are clever and must have put in a lot of studying, I keep finding myself going around in circles and getting confused, but it is good trying.
J.
One thing you mayfind useful:
Tools -> Formula Auditing -> Evaluate formula
with this you can step through the formula - it shows you what is about to be evaluated, and what has just been evaluated at each stage.
Try it.
Thanks for the tip Charlie,
I'm still on my Linux/OOo/calc box I'm afraid. My Windows/Excel laptop is still at the repairers waiting for a new LCD screen to come from Japan or China or wherever. Est another 3 weeks. So I can't find that tool in OOo, but I have been using the Trace Error/Fault/Precedent in OOo/calc. Probably not the same!
As it is, I still can't determine what is giving me the errors, which leaves me unsure if I'm chasing an OOo compatibility issue or something else. I have discovered that the errors are related to whether the spreadsheet is being used to enter data or has been saved/closed/re-opened.
This seems to be my first hurdle that I've been trying to explain and that I have been trying to find a solution for with your kind explanation of the formula. I need to get over this one, but I'm stumped at the moment and as I say, I'm also not sure if it is something I did wrong when I followed your instructions to enter the remaining referenced data.
What I am finding is, if I change the number of teams, I get the errors/problems below. If I save/close the spreadsheet (still in the error state) and then re-open it the errors are gone.
Sorry the following is a long list, but it is only to show an overview of the problem:-
Say I have just opened the spreadsheet and the last time it was saved I had entered 6 for the number of teams, then the formula produces (correctly) the following:-
If I now change the number of teams to another number (4 in this example), I get the #VALUE! instead of the date:-01/01/2010 Team one plays Team six Team two Team five Team three Team four 08/01/2010 Team six Team four Team five Team three Team one Team two 15/01/2010 Team two Team six Team three Team one Team four Team five 22/01/2010 Team six Team five Team one Team four Team two Team three 29/01/2010 Team three Team six Team four Team two Team five Team one 05/02/2010 Team six Team three Team two Team four Team one Team five 12/02/2010 Team five Team six Team four Team one Team three Team two 19/02/2010 Team six Team two Team one Team three Team five Team four 26/02/2010 Team four Team six Team three Team five Team two Team one 05/03/2010 Team six Team one Team five Team two Team four Team three
Another example with changing the number of teams to 10 is still #VALUE! errors (scroll down the list) and some dates/errors blank:-01/01/2010 Team one plays Team four Team two Team three Team four Team three Team one Team two Team two Team four Team three Team one #VALUE! Team four Team two Team one Team three Team three Team four Team two Team one Team four Team one Team three Team two
01/01/2010 Team one plays Team ten Team two Team nine Team three Team eight Team four Team seven Team five Team six Team ten Team six Team seven Team five Team eight Team four Team nine Team three Team one Team two Team two Team ten Team three Team one Team four Team nine Team five Team eight Team six Team seven Team ten Team seven Team eight Team six Team nine Team five Team one Team four Team two Team three Team three Team ten Team four Team two Team five Team one Team six Team nine Team seven Team eight Team ten Team eight Team nine Team seven Team one Team six Team two Team five Team three Team four Team four Team ten Team five Team three Team six Team two Team seven Team one Team eight Team nine Team ten Team nine Team one Team eight Team two Team seven Team three Team six Team four Team five #VALUE! Team five Team ten Team six Team four Team seven Team three Team eight Team two Team nine Team one #VALUE! Team ten Team five Team four Team six Team three Team seven Team two Team eight Team one Team nine #VALUE! Team nine Team ten Team eight Team one Team seven Team two Team six Team three Team five Team four #VALUE! Team ten Team four Team three Team five Team two Team six Team one Team seven Team nine Team eight #VALUE! Team eight Team ten Team seven Team nine Team six Team one Team five Team two Team four Team three #VALUE! Team ten Team three Team two Team four Team one Team five Team nine Team six Team eight Team seven #VALUE! Team seven Team ten Team six Team eight Team five Team nine Team four Team one Team three Team two #VALUE! Team ten Team two Team one Team three Team nine Team four Team eight Team five Team seven Team six #VALUE! Team six Team ten Team five Team seven Team four Team eight Team three Team nine Team two Team one #VALUE! Team ten Team one Team nine Team two Team eight Team three Team seven Team four Team six Team five
Last edited by Jo-Jo; 11-18-2009 at 09:21 AM. Reason: Hoping to correct message formatting.
Yah, I think it's compatibility, see screencap.
Thanks Charlie,
I can get the same results as your screen capture shows, but if I then change the number from 4 to anything else, it goes wrong... unless I save/close and re-open when it then picks up the changes.
As you say, probably an incompatibility between OOo and Excel, so as it's a pool league fixture, I can consider myself well and truly snookered.
I'll see if a friend has Excel and OOo installed and test the compatibility theory in a day or so, but I think this is the end of the road for this project.
All I can do is SINCERELY thank you for all of your time and effort, I can't express how grateful I am.
Thank you VERY much Charlie.
JoJo.
Hi all,
Charlie, for info, I tried your sheet on a friends Excel machice and it works with no problems. So definately a compat issue.
I've asked the OOo forum if they can spot what Calc doesn't like about it.
J.
Hi all,
Just in case anyone looks at this thread at a later date (not that it is an Excel issue).
The eventual problem turned out to be an incompatibility between Excel and the Ubuntu version of OOo-Calc. It appears (although no one can quantify) that the Ubuntu version of OOo-Calc can confuse the 'seperators' (commas v semi-colons), possibly related to locale settings.
Changing from the Ubuntu to the standard Sun version of OOo resolved the problem.
Just for info.
J.
Thanks for returning this valuable information to the board, J.
You're more than welcome Charlie. I hate coming to the end of a thread without a solution or acknowledgement... like a book with the last page missing.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks