Excel Formula

[JohnLJ]

Hi I'm stuck trying to work out a formula for the following scenario based on a family competition we run during the six nations:

Each family member tries to predict the weekly scores. Point are then awarded as follows: Two points if you predict the correct winner (or your forecast a drawn game….), two points if you predict the exact number of points each team gains; one point if you correctly predict the number of points each team gains within 5 points either side. You can play one joker each week which doubles your earned score. So for this weeks Irish game:

	A	B	C	D	E	F	G	H	I
1​				Prediction Ireland	Prediction Wales	Actual Irish Score	Actual Welsh Score	Joker	Points Earned
2​	Ireland	v	Wales	28	18	29	7	Y	6

Any assistance gratefully received.

Thanks

 

[steve the fish]

You can just build in stages eg:

=IF(H2="Y",2,1)*(IF(OR(AND(D2>E2,F2>G2),AND(D2<E2,F2<G2),AND(D2=E2,F2=G2)),2,0)+IF(D2=F2,2,IF(ABS(D2-F2)<=5,1,0))+IF(E2=G2,2,IF(ABS(E2-G2)<=5,1,0)))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=(OR(AND(D2>E2,F2>G2),AND(E2>D2,G2>F2),AND(D2=E2,F2=G2))*2+(ABS(D2-F2)<=5))*((H2="Y")+1)

 

[JohnLJ]

=IF(H2="Y",2,1)*(IF(OR(AND(D2>E2,F2>G2),AND(D2<E2,F2<G2),AND(D2=E2,F2=G2)),2,0)+IF(D2=F2,2,IF(ABS(D2-F2)<=5,1,0))+IF(E2=G2,2,IF(ABS(E2-G2)<=5,1,0)))

Thanks Steve
You are a star.
John