Football Prediction Sheet Help

[svidoy]

Hi,

I did try and put this in the 'Formulas and Functions' but the page wouldn't load.

I run a football prediction thing at work and am trying to create a spreadsheet to make life a lot easier. I found one online and have slightly changed it to meet what i need, but there is one part i can not work out.

I have tried contacting the person who made the original but have had no reply.



Basic example for a win

Prediction: Man Utd 3 - 1 Swansea


Man Utd Win and Correct Score = 7 Points. (Eg 3-1)
Man Utd Win and Man Utd Score Correct Goals = 4 Points (Eg 3-2)
Man Utd Win and Swansea Score Correct Goals = 4 Points (Eg 2-1)
Man Utd Win and Neither Correct Goals = 3 Points (Eg 4-0)
Man Utd Lose and Swansea Score Correct Goals = 1 Point (0-1)
None of the above = 0 Points (Eg 0-5)


Basic example for a Draw

Prediction: Man Utd 2 - 2 Swansea

Man Utd Draw and Correct Score = 7 Points (Eg 2-2)
Man Utd Drawn and Other Score = 3 Points (Eg 1-1)
Man Utd or Swansea Win But Score Correct Goals = 1 Point (Eg 2-0)
None of the above = 0 Points (Eg 3-1)


I hope that makes sense!

From my Spreadsheet, it all seems to work, except when there is a correct result and 1 team has the right amount of goals. Sometimes it works and sometimes it doesn't.

Eg

Prediction: Man Utd 1 - 2 Swansea, lets use the result Man Utd 1 - 3 Swansea. This should produce 4 points (3 for Swansea win + 1 for correct Man Utd score), but my spreadsheet only displays 1 point, so its not understanding to include the win for Swansea.


However, the following will show 4 points.

Prediction: Man Utd 3 - 1 Swansea with a result of 2 - 1. (3 Points for Man Utd win + 1 point for correct Swansea score)

I am not sure why this works for some and not others......

My spreadsheet does seem to work correctly for draws.


I have attached the spreadsheet if someone needs to play with it. (Rows W-BD are normally hidden)

Thanks to anyone that can help me

[micope21]

Hi

I have to remove V between the score on both Columns in order to work?

Sam Sheet

=IF(COUNT($J15:$K15,$P15:$Q15)=4,(1*OR($J15=$P15,$K15=$Q15))+(3*(SIGN($J15-$K15)=SIGN($P15-$Q15)))+(3*AND($J15=$P15,$K15=$Q15)),"")

copy down

See the file! hightlight yellow!

Regard

[svidoy]

Thanks for your help, all seems to be working fine, will check it over the weekend and see how it goes.