Excel Formula Required Please !!!

[IanD]

Hi,
I hope somebody can help me with the required formula because I'v
tried and failed.
I am trying to create a spreadsheet for football predictions.
I will use just one line for simplicity.
Lets say column A is the persons prediction (ie: Home Win). Column B i
the persons predicted score (ie: 2-1).
What I want to do is have two more columns where I enter the actua
result (ie: Home Win) and the actual score (ie: 2-1).
What I want to do is when I enter those correct elements I want it t
transfer 1 point into the persons score box for a correct predictio
and then 5 points into the persons score box for the correct score an
then total them up (Alternatly I could have two scores boxes)
Obviously if the get it wrong (or part of it) I want a zero transferre
into their scores boxes.
Any ideas please folks??

 

[Domenic]

Hi,

If you're using a spreadsheet that looks something like this,

Name Predicted Result Predicted Score Actual Result Actual Score
Score Box
Joe Home Win 2-1 Home Win 2-1 6
John Away Win 3-2 Away Loss 3-2 0
Jake Home Win 3-2 Home Win 3-1 0
Jill Away Win 4-3 Away Win 4-3 6

you can enter the following formula in cell F2, and copy down:

=IF((B2=D2)*(C2=E2),6,0)

Hope this helps!

 

[Manish Bajpai]

=IF(AND((A2=C2),(B2=D2)),5,0)+IF(A2=C2,1,0)

Where, Col. A is prediction for Win,
Col. B is predicted score.
Col.C is actual result - win .
Col.D is actual score.

Thank,

Manish