Need advice with this formula for my football predictions

[Benson08]

Hey, I run a football (soccer) predictions league for me and my friends and after so many years of doing it all manually i want to make it work with formulas but i cant get them to work properly, im very close to completing it but theres always 1 result that doesnt work properly.

Here is the point system...

§ 7 Points: Correct score & correct first team to score
§ 5 Points: Correct score only
§ 4 Points: Correct result & correct first team to score
§ 2 Points: Correct result
§ 2 Points: Correct first team to score first

Here is what the cells contain

A3 = Home Team
B3 = Actual Home Score
C3 = "Versus"
D3 = Actual Away Score
E3 = Away Team
F3 = Which team scores 1st, H for Home, A for Away, X for Nobody
G3 = Predicted Home Score
H3= "Versus"
I3 = Predicted Away Score
J3 = Predicted team to score 1st, H for Home, A for Away, X for Nobody

Here is my formula...
=IF(AND(B3=G3,D3=I3),5,IF(AND(D3>B3,I3>G3),2,IF(AND(B3>D3,G3>I3),2,IF(AND(D3<b3,i3<g3),2,0))))+if((f3=j3),2,0)[ formula] It works fine but if the actual result is 0v0 and i predicted 1v1, i should recieve 2 points for the correct result but i dont get any points Any more info just ask :D thanks for your help people!</b3,i3<g3),2,0))))+if((f3=j3),2,0)[>

 

[Benson08]

here is the formula due to it not being all there <b3,i3<g3),2,0)))) +if((f3="J3),2,0)</html"></b3,i3<g3),2,0))))>=IF(AND(B3=G3,D3=I3),5,
IF(AND(D3>B3,I3>G3),2,
IF(AND(B3>D3,G3>I3),2,
IF(AND(D3<B3,I3<G3),2,0))))
+IF((F3=J3),2,0)

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Maybe...

IF(AND(B3=G3,D3=I3,5),5,IF(OR(AND(B3>D3,G3>I3),AND(D3>B3,I3>G3),AND(B3=D3,G3=I3)),2,0))+IF(F3=J3,2,0)

M.

 

[Benson08]

hello M. this new formula seems to work just fine! I cant thank you enough :D I have trailed it and it appears fine, its theres any more problems im be sure to update you, thanks again

 

[Marcelo Branco]

hello M. this new formula seems to work just fine! I cant thank you enough :D I have trailed it and it appears fine, its theres any more problems im be sure to update you, thanks again

You are very welcome and thanks for the feedback.

Just curious, how many points for: actual: 0x0; prediction: 0x0, 5 or 7?

M.

 

[Marcelo Branco]

Just for the records:
There is a typo in my formula, but that, fortunately , doesn't cause any problem.

This 5 (red) is superfluous.
=IF(AND(B3=G3,D3=I3,5),5,IF(OR(AND(B3>D3,G3>I3),AND(D3>B3,I3>G3),AND(B3=D3,G3=I3)),2,0))+IF(F3=J3,2,0)

M.

 

[Benson08]

hey M,

You receive 7 points for 0v0 X (correct score and predicted both 0v0 and no team to score first ) yeah i wondered why that was there but it didnt affect the formulas so i left it in :P

I use the H/A/X in the formula instead of the cell colour as i dont have a colour how to use VBA haha, i normally highlight with team scores first in yellow on my pictures i post on facebook, so i inserted the new columns for the first goals H/A/X for the formula use

 

[Marcelo Branco]

Yes, 0x0 ia an ugly match (no goals ) and a pessimistic prediction, but it deserves 7 of course.

M.

 

[Benson08]

haha yes it is... if someone forgot to put their predictions in i used to put it as 0v0 so they got 7pts if it actually was :/ lol its now 3v3 with no goalscorer, keeps them outta the points :P they'll be lucky to get 2 points now