football prediction game help

Hi all

I am new on here and was hoping for a little help. My question is similar to one on the following thread, but I dont think mine is quite as complicated. I was unable to sort from the answers on this thread so was hoping someone could help with a formula for my document....

http://www.excelforum.com/excel-form...t-formula.html


Basically, I have started a football prediction game. The scoring works like this:

5 points for a completely correct result and scoreline for both teams e.g. 1-0 prediction and 1-0 result
2 points for a correct result but not correct scoreline e.g. 1-0 prediction and 2-0 (or 2-1 etc) result
0 points for an incorrect result e.g. 1-0 prediction and 2-2 (or 0-1 etc) result


My formula's in rows I-L are nowhere near how they need to be, and that is as far as my abilities in excel go really!

Chris - he guessed 0-1 - he got the result and the scoreline correct, therefore should be 5 points
Dave - he guessed 1-3 - he got the result right but the scoreline wrong, therefore should be 2 points
Freddie - he guessed 0-2 - he got the result right but the scoreline wrong, therefore should be 2 points
Graham - he guessed 1-1 - he got the result incorrect and therefore should be 0 points
Liam - he guessed 0-1 - he got the result and the scoreline correct, therefore should be 5 points
Theo - he guessed 1-0 - he got the result incorrect and therefore should be 0 points


Although it does not take that much to type it in, it would be very handy to know a formula to use for this. I want to be able to just type the result into the fixture tab, and then it automatically fill in columns H and I for me, adding the total points accrued into I41.

Any help would be very appreciated....

Thanks in advance...Tottenham Fixture Game.xlsTottenham Fixture Game.xls

In the attached file I've used columns K and L of the players' sheets to give the result (as H, A or D) for predicted and actual, using this formula in K2:

Formula:

=IF(G2="","",IF(LEFT(G2,FIND("-",G2)-1)=RIGHT(G2,LEN(G2)-FIND("-",G2)),"D",IF(--LEFT(G2,FIND("-",G2)-1)>--RIGHT(G2,LEN(G2)-FIND("-",G2)),"H","A")))

which is then copied to L2, and both are copied down. Then the points can be calculated in I2 using this formula:

Formula:

=IF(OR(G2="",H2=""),"",IF(K2=L2,IF(G2=H2,5,2),0))

again, copied down. These have been applied to all players' sheets.

Hope this helps.

Pete

Pete_UK & duffry - thank you so much for your prompt responses, both of them work brilliantly. i will be able to sit back and let it do itself now. it gives me a little more knowledge and will be useful in my job for the future, so thanks again!

just one final question, on the Leaderboard tab, can you think of a any formulas to have this automatically update so that the name and current highest scores updates itself?

all I was intending to do was just use Data/Sort - however for completeness if this could be done it will finish it off completely...

I've set this up in the attached file for you, so it should now automatically adjust.

Hope this helps.

Pete

Hi there peeps. I'm new to this place but thought you guys could help me out with my Prediction league on excel 2010.
I used one of the formulas posted on this site which worked nicely with couple of minor tweaks, however I'm struggling with a couple of bits.

predo.jpg
Hi there peeps. I'm new to this place but thought you guys could help me out with my Prediction league on excel 2010.
I used one of the formulas posted on this site which worked nicely with couple of minor tweaks, however I'm struggling with a couple of bits.
How can I stop excel counting blank cells as 5 points (cell I7 in the image should be 0 or preferably blank) Many thanks, Al