Formula to award points for football score prediction

**OliverW** · 06-07-2008, 08:45 AM

Hope this is the right subforum to post in! Basically i'm writing a football table and want to match the scores in a prediction to the actual results and displaying a score based on the predictions

so for example if you predict the game will end 2-1 and it does end 2-1 return the value 3. If you select the correct WINNER of the match you'll get 1 point and if you don't get the winner right you get 0 points.

The formulae i tried was

=IF(AND(B3=K3,C3=L3), D3=3, IF(AND(B3>K3, C3>L3), D3=1, IF(AND(B3<K3, C3<L3), D3=1)))

Any help appreciated! Thanks.

**daddylonglegs** · 06-07-2008, 09:58 AM

Hello Oliver,

If you're putting the formula in D3 then you wouldn't normally refer to that cell within the formula. Try this

=IF(COUNT(B3,C3,K3,L3)=4,IF(AND(B3=K3,C3=L3),3,IF(SIGN(B3-C3)=SIGN(K3-L3),1,0)),"")

**mudraker** · 06-07-2008, 10:16 AM

OliverW

Welcome to Exceltip forum

Please take a couple of minutes and read the Forum Rules then edit your thread title by following the instructions in the rules (Rule 1)

**OliverW** · 06-07-2008, 10:36 AM

Thanks for both replies, firstly because it's fixed and sorry for breaking the rules! I've read them and now fully understand. As this topic is fixed you may lock/delete it if you wish.

Thanks so much.

**daddylonglegs** · 06-07-2008, 10:56 AM

Hello Oliver,

mudraker was, of course, correct. I should have commented on the thread title myself. I have now changed it to something a little more descriptive of your problem.....

**pluto2709** · 12-30-2008, 07:09 AM

Originally Posted by daddylonglegs

Hello Oliver,

If you're putting the formula in D3 then you wouldn't normally refer to that cell within the formula. Try this

=IF(COUNT(B3,C3,K3,L3)=4,IF(AND(B3=K3,C3=L3),3,IF(SIGN(B3-C3)=SIGN(K3-L3),1,0)),"")

Hello daddylonglegs

i know it's been a while since you posted this but i was hoping you might be able to help me out with a little update. Your formula works fine with the exception of.. if the score ends in a 1-1 draw, anyone who has predicted a draw (ie 0-0 or 2-2) is awarded 1 point. when in fact they should be awarded 0 points. i have been trying to develop this sedsheet for ages and you formula was a great help to me. i hope you can come back with a suggestion.

regards
pluto

**daddylonglegs** · 12-30-2008, 08:52 AM

Try changing to

=IF(COUNT(B3,C3,K3,L3)=4,IF(AND(B3=K3,C3=L3),3,IF(B3<>C3,IF(SIGN(B3-C3)=SIGN(K3-L3),1)))+0,"")

Formula to award points for football score prediction

LinkBack

Thread Tools

Rate This Thread

Display

Formula to award points for football score prediction

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions