HELP.. Need to write a formula

[DannyTheo]

Hi All,
New to this but i heard this was the place to find answers to my excel questions.
Ok so here is what i am trying to do.
On one sheet it has a table which has the results to football games
On the same sheet it has the peoples predictions
On the second sheet it has a League table.
On the league table i need a forumal that will do the following..
IF person 'Tom' has a Prediction of 2-1 and the result is 2-1 then award 5 points in league table. If result is 2-0 then tom will recieve 3 points and if the result is 2-3 then tom recieves 1 point.
I have been trying if and or then formulas all moring and cant get it to work PLEASE HELP
Thanks
Dan

 

[DannyTheo]

Anyone got any ideas?

 

[SteveO59L]

Are the scores populated in seperate cells, or just in 1 ?

 

[DannyTheo]

In seperate cells bud.

 

[SteveO59L]

Ok

with Tom in A1, and his score forecast in B1 & C1

In e1 & f1, the correct result, and in g1, the points earned


=IF(AND(E1=A1,F1=B1),5,IF(AND(E1>F1,A1>B1),3,IF(E1=A1,1,0)))


I remember around the time of the last world cup, there were a number of really professional league tables produced. You might be able to lift some ideas from one of those

 

[DannyTheo]

Looking at your formula you dont mention result C1 but you use A1 a few times and if A1 is just the player 'tom' then i dont see why that is used.
Have tried it out on my spreadsheet and it doesnt seem to work
I hate this silly thing.. dont know why i told my dad i'd give it a go for him lol

 

[SteveO59L]

Apologies I missed out Tom, and started the scores in A1

 

[scottylad2]

Excel Workbook
	F	G	H
35	Home Team	Away Team	Points
36	2	1	5
37	2	2	3
38	0	3	1
tests

 

[DannyTheo]

ScottyLad2 thanks for your help but i dont see how this compares the Actual match result to the predictions set.
For example

Liverpool 1 Spurs 0

Tom's Prediction was Liverpool 1 Spurs 1

With our scoring system he would be awarded one point for this as he guessed the liverpool score.

If 'Prediction' = 'Result' & 'Score' Then award 5 points
If 'Prediction' = 'Result' then award 3 points
If 'Prediction' = 'Score' then award 1 point

Getting this into a formula seems to be the hardest thing i have tried to do in excel Please help...

 

[DannyTheo]

oh my god!!!!
I got it working
=if(and(result1=predict1,result2=predict2),5,if(and(result1>result2,predict1>predict2),3,if(and(result1=predict1),1,if(result2=predict2,1,0))))

Now i just have to type that out for the ten games and the 20 players of the prediction league we got going!!!

Thanks to all for your help

Dan