Formula Help Please.......... Bit Confused !

[Russ At Index]

Hi All,

Here goes....
C4 Actual Home Score
D4 Actual Away Score
E4 Joker ( Doubles Pts Score if Played )
F4 Predicted Home Score
G4 Predicted Away Score
H4 Has the following formula:
=((SIGN(C4-D4)=SIGN(F4-G4))+(C4=F4)*(D4=G4)*3)*IF(E4="Joker",2,1)

Therefore you will get 1 pt for a correct outcome ( home win although
your prediction was incorrect)

3 pts for a 100% correct prediction

& If you have played your Joker in in cell E4 , you will double your
pts score ( providing you have pts to double)

Formula above is not reporting correctly....... convinced it did last season !

Can someone point me in the right direction ?

Thanks In Advance

Russ

 

[brian.wethington]

Perhaps this

=((SIGN(C4-D4)=SIGN(F4-G4))+(C4=F4)*(D4=G4)*2)*IF(E4="Joker",2,1)

If you are wanting 3 points total for the 100 percent correct prediction.

 

[Russ At Index]

Hi Brian,

Thanks for the response.

I have adjusted my formula to reflect your version.
Unfortunatley it does not calculate correctly.

The first score line in cells c4 & d4 is 3 & 2.

If i adjust the predicted score in cells f4 & g4 to 3 & 2
the formula reports 1pt as opposed to 3pts !

Any ideas gratefully received !

Thanks

Russ

 

[Andrew Poulsom]

If Actual is 3-2 what would you expect for these predictions (and why)?

4-2
3-3
3-2
3-1
2-2
2-1

 

[Russ At Index]

Hi Andrew,

Trust you are well.

4 - 2 As the actual result
4 - 2 As the prediction of my colleague should return 3 pts
1 - 0 As the prediction of my colleague should return 1 pts
3 - 3 As the actual result
1 - 0 As the prediction of my colleague should return 0 pts

The predictions for all fixtures have already been submitted.

The absolute correct prediction against the result = 3pts
The correct outcome - Home Win predicted, home win actual,
but the scores differ 1 pts

All other variants = 0 pts

Kind Regards

Russ

 

[Andrew Poulsom]

So 1 point for correctly predicting home, away or draw, plus 2 points for correctly predicting the scores:

=((SIGN(C4-D4)=SIGN(F4-G4))+((SIGN(C4-D4)=SIGN(F4-G4))*(C4=F4)*(D4=G4))*2)*IF(E4="Joker",2,1)

 

[brian.wethington]

Perhaps this

=((SIGN(C4-D4)=SIGN(F4-G4))+(C4=F4)*(D4=G4)*2)*IF(E4="Joker",2,1)

If you are wanting 3 points total for the 100 percent correct prediction.

For your example (3 Home, 2 Visitor actual; 3 Home, 2 Visitor predicted) I get 3 points. Of course the new formula presented by Andrew does as well.

Edit: BTW, there is only one difference in Andrew's formula and the one I presented. multiplied the (c4=f4)*(D4=G4)*2 with ((SIGN(C4-D4)=SIGN(F4-G4)). This will only be one or zero and therefore will not make any difference to the outcome of the answer as if it was zero then c4 = f4 is not true or d4=g4 is not true therefor making it zero already. if it is 1 either of those could still make the product 0 but if both are true it will make the product 2.

 

[Russ At Index]

Brian, Andrew.

Many thanks chaps, problem solved.

Kind Regards

Russ