Footy

[MRG]

Can anyone think of a formula for footy scores, ie 5
points for correct score. 3 points for right result but
incorrect score. & 0 for incorrect result.

 

[Norman Harker]

Hi MRG!

Try:

=IF(AND(A1=C1,B1=D1),5,IF(OR(AND(A1>B1,C1>D1),AND(A1=B1,C1=D1),AND(A1<B1,C1<D1)),3,0))

 

[MRG]

Seems to work cheers.

 

[Norman Harker]

Hi MRG!

Thanks for the confirmation. Always useful for Google searchers.

 

[Dana DeLouis]

Not sure, but could this section be reduced to the following?

IF(OR(AND(A1>B1,C1>D1),AND(A1=B1,C1=D1),AND(A1<B1,C1<D1)),3,0)

=>

IF(SIGN(B1-A1)=SIGN(D1-C1),3,0)

Dana DeLouis

 

[Norman Harker]

Hi Dana!

I think MRG is off playing soccer now but:

=IF(AND(A1=C1,B1=D1),5,IF(SIGN(B1-A1)=SIGN(D1-C1),3,0))

Works OK and is a neater solution.

--