Am I dreaming if I want to automate scoring in my World Cup Comp S/sheet

[cRx45]

Hi all. This is my first post here. I was referred here by Mike at ExcelisFun on youtube. He suggested I ask you fine people to see if anyone can help me with my <strikethrough><del>quandary</del></strikethrough> labour of love .

Every 4 years when the World Cup is on my friends and I run a small world cup predictions competition between ourselves. We all put in $50 each and the top 3 win some ca$h.

It started back in the 2002 world cup, being the 'computer guy' in the group and at the time knowing a little bit about Excel, I decided to create a 'small' spreadsheet to keep track of everyone's progress during the event. As I also knew a bit of html I worked out how to save the excel file as html and uploaded it to my server for all to see their progress via the web. What started as a 5-6 person small comp back in 2002 (which we started 1 week into the event) has now turned into an elaborate spreadsheet which I've managed to automate the line up and scoreboard using Vlookups, Ranking and some conditional formatting.

By the next world cup next year, I'd like to finally automate the scoring process, so that when I enter the official score after each game, I'd like excel to automatically work out each persons score (I wish!). BUT... we have so many ways of scoring that I have no idea where to start this process and what formula or combination of formulae to use. I was thinking maybe nested ifs but I think it's going to be really complicated. There's a need for conditional formatting too as the scoring is also colour coded.

I was hoping someone could guide me on what the best way to tackle this would be. I'm not looking for someone to 'do it', just to look at it and suggest ways to go about it and what formulae you would use etc.

Anyway, if anyone is interested in looking at it, I've posted the html of the last comp we did back in 2010. It's just the html file. You can't see the excel formulae, but you can see all the different ways of scoring (the scoring is described in detail and found in the tab at the bottom named scoring description).

Seth Efrica 2010 World Cup Comp v3.0 - Results Page

If you want me to post the actual excel file (if I can here), so you can see further, let me know.

Also, if you like it and you're into the world cup, feel free to use it for your own comp next year. It's been a labour of love of mine for the past 10 years that I'm happy to pass on.

Anyway thanks for reading and if you can, let me know what you think, even if it's to say "no way, you're dreaming!".

Cheers
cRx45

 

[circledchicken]

Hi and welcome to the forum,

Maybe these similar spreadsheets might give you a few initial ideas:
FIFA Worldcup Excel Spreadsheets [Roundup] | Chandoo.org - Learn Microsoft Excel Online

If you have specific areas / formulae / layout choices for parts of your spreadsheet that you are unsure about post back and hopefully someone can help.

 

[cRx45]

Hi CircledChicken,

Thanks for the link. Unfortunately that's not what I'm after as they're just trackers (I actually used the 5th one "Worldcup Planner with team points" in combination with my spreadsheet to hand out to all players so they could give me their 'predictions' before the tournament started as that's part of where some of the points you can get come from).

None of those 'trackers' have the type of scoring system we've created. We've just made up different ways to score points and I'm trying to work out how to write a 'formula' to work out all the different combinations/ways to score, but do it automatically.

I'm pretty sure my spreadsheet is too complicated to do it. It's gonna be a pretty loooong and complex formula if I ever work it out.

Thanks anyway.
cRx45

 

[circledchicken]

Hi cRx45,

It doesn't need to be extremely complex. If you structure the spreadsheet efficiently it should be possible to get all the parts to work together and calculate the points for each player based on a series of smaller step-by-step calculations.

Here are some best practice design instructions you can have a look at as a first step:
Excel Best Practices. Efficient Excel Spreadsheet Designs in Spreadsheets

If you want to post your previous spreadsheet to skydrive or dropbox etc and post the link here, I or someone else can have a look and try to help where possible.

 

[cRx45]

Thanks again CircledChicken.

If anyone wants to see how complex it is, the dropbox link is: https://www.dropbox.com/s/rsexu0ba8pyxt92/WorldCup2010ScoreChart.xlsx?v=0swn-

The reason why I think it's going to be difficult is because if you look at Round 1 for instance, on lines 36 and 37, there are a few different ways to score. A full description of these scores is found on the Points Description tab.

It's going to be difficult because I think it will need nested IF's. There's also colour coding of the backgrounds conditional on the points scored and there are also some conditional borders for the 'Possibles'. The possibles were a quick way to highlight/stand out who had a possible type of score because it was different from all other peoples predictions. That part's just for suspense.

Yes we've (I've!) made it a bit complex, but it adds to the experience.

If anyone's interested in suggesting how I approach this, thanks in advance.

Cheers
cRx45

 

[Petronella]

Hey

That's pretty cool.

I'm sure it's possible to do what you after. The problem is it is such a lot to hold in your head at the same time.

If it were me I would be creating a load of extra helper columns, at least to start with. Some of these would be repeated for each competitor and some would be one-offs related to the tricky score comparisons. At the end you might be able to combine the individual competitor ones into your complex "if" statements, but building it up bit by bit would allow you see the wood for the trees. If you can't combine into a single cell for each competitor/result combination you could maybe hide some of the helper cells.

If you've got this far I'm sure you can crack this next step!!

Any help?

 

[cRx45]

Yes, thanks, very helpful.

I'm going to have to sit down and put my thinking cap on.

Cheers
cRx45

 

[circledchicken]

I had a quick look at your file and I am impressed! Looks like you've put a lot of effort into this and it is very well presented (although the current design is not particularly suited towards automating the scoring).

What version of Excel are you / will you be using?

 

[cRx45]

I had a quick look at your file and I am impressed! Looks like you've put a lot of effort into this and it is very well presented (although the current design is not particularly suited towards automating the scoring).

What version of Excel are you / will you be using?

Thanks. Yes it's been a labour of love since 2002. I'm currently using version 2010.

 

[circledchicken]

Here's a experimental layout for an automatic scoring calculation engine for your spreadsheet based on traditional excel:
http://sdrv.ms/Z0S95C

With this kind of setup, you can then add a separate front-end which doesn't itself hold much of the data / calculations, but links into the background engine and looks good with your pictures, conditional formatting etc.

There are also a large number of other changes you may want to make in terms of further normalizing the tables, error checking, highlighting the input/calculation/output sections, perhaps organising the calculations in a different way, order etc. Maybe explore using Data Tables (Calculate multiple results by using a data table - Excel - Office.com) as well.

Since you are on Excel 2010 though, if you have time you might want to explore PowerPivot (PowerPivot | Microsoft BI). It would offer a very good alternative way to explore for doing this although there will be a bit of a learning curve.

Also, on a slightly controversial note, I think Mike G should have ended up on 83 points tied with Sylv C for 2010. In my opinion he should have lost 2 points in game 61 (Golden Dumbass), although it might be you haven't done this because Michael N's prediction was missing for that game - I think that case should still be a -2 (and that's how I've handled missing predictions in my sample spreadsheet).