Excel help please

Silver-Cat

HIya, if you PM me I don't mind creating a simple to use spreadsheet for you.

I am a bit of an excel geek and probably already have something ready that might need a bit of tweaking for details that I can do for you. ( I have hundreds of templates for various different type of functions)

slim1690

EchoLocation wrote: »

It might by a daft question but what do the F, A and LD columns consist of. Based on the formula it looks like a For and Against, but what are these values?

Also, can I assume that the max number of games someone could play is 12?

It is For (Legs Won) Against (Legs Lost) and Legs Difference

All matches are best of 14 legs, 7-7 is a draw, or first to 8, everyone plays each other once, so 7 matches in a league of 8.

EchoLocation

slim1690 wrote: »

It is For (Legs Won) Against (Legs Lost) and Legs Difference

All matches are best of 14 legs, 7-7 is a draw, or first to 8, everyone plays each other once, so 7 matches in a league of 8.

Ok that makes sense. I'll have a play around if I get some time and see what I can come up with.

slim1690

EchoLocation wrote: »

Ok that makes sense. I'll have a play around if I get some time and see what I can come up with.

The main problem I've found using template spreadsheets is that they won't allocate a bonus point for an 8-0 win and deduct a point for an 8-0 defeat. (That's the BP and DP columns)

Thanks

EchoLocation

slim1690 wrote: »

The main problem I've found using template spreadsheets is that they won't allocate a bonus point for an 8-0 win and deduct a point for an 8-0 defeat. (That's the BP and DP columns)

Thanks

Which is why it's best to build your own. Templates are fine until you have to start messing with them.

Building it from the ground up I'd say you need 4 things split out...
1) A list of the players names, division, contact details etc.
2) A results list where you enter the player, date, opponent, the various results points etc. after each match. I'd to this for each player separately - so each match will have result for each player.
3) A list of the players to total up all of their results as they're input and then assign a ranking based on the total points.
4) A league table to look at 3) and then display the results based on ranking.

As I said, I'll have a play around and see what I can come up with.

EchoLocation

I've not forgotten about this, I have made a start but I've not had the time I thought I would to give it proper attention. I'll try and upload the finished file in the week.

As a quick overview of the plan...

5 Worksheets
1) Player Details
2) Match Results
3) Match Totals
4) League Tables
5) File Variables

I'm just debating how 'intricate' to make it to aid any future changes, as more complicated now may equal simpler changes in future. Just not sure to what level at the moment.

1) Player Details
This will hold the Player Name and Division, it will also act as a validation list for selecting the player name on the Match Results sheet. You can also add additional columns for contact details, phone/email/address etc, and a general Notes column.

2) Match Results
This is where you will record the results of each match. The columns I'm thinking of so far are Date, Player Name, Opponent Name, Match Type, Legs Won, Legs Lost, Match Result, Bonus, Deduction, 180's, Highest Checkout and Match Note. The Player Name and Opponent Name columns can have the validation list from sheet 1) applied. The Match Result, Bonus and Deduction columns will have formulas.

3) Match Totals
This is where the all the totalling will take place for each player based on the values entered on the Match Results sheet. It will hold the totals for each player as well as the points won based on those totals. It will also hold the ranking positions and the assigned season final positions. It will be split into the separate divisions. I don't have Excel at home, so I'm using LibreOffice which means that I can't use the SUMIFS function I normally would with the type of formulas needed. Instead there'll be the older approach of (more complex looking, but not really) array formulas. It will be an .xls file so it shouldn't look too different, if at all, in Excel.

4) League Tables
This is where the league tables will be displayed. They will automatically update based on the ranking postions held on the Match Totals sheet. The formulas will all be straightforward vlookup's.

5) File Variables
This will hold the different variable values for use with any columns that may use validation or formulas that need to lookup a comparison value. The reason for this is to make any match format changes easier so you won't need to update lot's of formulas any time a change to, for example, the number of points awarded changes etc.

slim1690

That sounds absolutely superb. One additional stat to enter in match results would be short legs (legs finished under 20 darts) represented as numbers, ie 14 for a 14 dart leg, or 17 for a 17 dart leg etc.

Thanks

bazster

Another misguided attempt to build a database using Excel. What is being described would be trivial in Access.

EchoLocation

bazster wrote: »

Another misguided attempt to build a database using Excel. What is being described would be trivial in Access.

Yes it would, but the OP is a basic Excel user and almost certainly will have no clue about Access. This is a perfectly fine project to tackle in Excel; it's not misguided in the slightest, but thanks for your helpful input anyway.

bazster

But he's got you doing it for him because he doesn't know how to do it in Excel either. If he needs to learn he might as well learn how to do it properly, and anyway, in my opinion learning to construct a simple database is considerably easier than learning some of the Excel features you are proposing to use.

Excel is very good at what it does but it is NOT a database technology.

Still, when all you've got is a hammer, I guess everything looks like a nail.