My Precitions Spreadsheet - Help

[IanD]

Hi folks,
I hope you can help me. I want to create a spreadsheet for a football scores predictions competion i'm running with some friends.
What I need to do is have a column for each of the predicted scores per person then I need to have a column for the actual score.
Now here's the tricky bit. Each person gets 1 point for predicting that the home team will win lose or draw and on top of that they get 5 points if they predict the correct score.
I need a formula that, when the correct scores are added to one column it will then create a total of points accrued for each person in a seperate column next to thier prediction - I have tried long and hard to achieve this and am struggling. Anyone out ther that can help me please ?
Thanks in advance,
Ian

[Niek Otten]

It is best to enter a score in two columns; that way you can make exact
comparisons without having to extract digit from a text string.
I give an example for one person, easily extended to more.
Put the actual score in column A and B. So 1-0 would be a 1 in column A and
a 0 in column B
The predicted scores are in C and D.
In column E:
=IF(SIGN(A1-B1)=SIGN(C1-D1),1,0)+IF(AND(A1=C1,B1=D1),5,0)
Copy down as far as needed.
In F1:
=E1
In F2:
=F1+E2, copy down

--
Kind regards,

Niek Otten

"IanD" <[email protected]> wrote in message
news:[email protected]...
>
> Hi folks,
> I hope you can help me. I want to create a spreadsheet for a football
> scores predictions competion i'm running with some friends.
> What I need to do is have a column for each of the predicted scores per
> person then I need to have a column for the actual score.
> Now here's the tricky bit. Each person gets 1 point for predicting that
> the home team will win lose or draw and on top of that they get 5 points
> if they predict the correct score.
> I need a formula that, when the correct scores are added to one column
> it will then create a total of points accrued for each person in a
> seperate column next to thier prediction - I have tried long and hard
> to achieve this and am struggling. Anyone out ther that can help me
> please ?
> Thanks in advance,
> Ian
>
>
> --
> IanD
> ------------------------------------------------------------------------
> IanD's Profile:
> http://www.excelforum.com/member.php...fo&userid=9885
> View this thread: http://www.excelforum.com/showthread...hreadid=488454
>