Comparison:get a correct score

[:-)]

Hi all,

I have a football (that's soccer for those non UK residents!) prediction league with my brother and have decided to start using Excel to work out our scores,

I have set up a spreadsheet where I enter our predictions and the actual score and would like Excel to compare the various cells.

If we get a correct score, we get 3 points, a correct result(ie if we predict a home win and the game ends in a home win but not the score we predicted) 1 point, and no points for a completely wrong prediction.

For the layout of the spreadsheet so far, please see the attachment.

Hoping someone can help me.

Many thanks,

Lee

[sweep]

Hi,

In O3

=IF(C3&E3=K3&M3,3,IF(OR(AND(C3>E3,K3>M3),AND(C3<E3,K3<M3),AND(C3=E3,K3=M3)),1,0))

should get you started

[:-)]

sweep,

That's exactly what I was looking for.

Huge thanks for a quick response!

[:-)]

Just thinking, is there any way I could add in a condition that if the Actual Score box is empty, it will display "-" as otherwise, Excel calculates the two empty boxes as being a draw.

Thanks

[sweep]

=if(c3&e3=k3&m3,3,if(or(and(c3>e3,k3>m3),and(c3<e3,k3<m3),and(c3=e3,k3=m3)),1,if(k3&m3="","-",0)))

[:-)]

Thanks, thats almost there, but if either of us predict a draw, then even if the Actual Score cells are blank, it shows us as getting a point.

(possibly because if K3 and M3 are both empty, Excel treats them as the cells being the same and calculates it as a draw?)

[sweep]

This is why someone else should always check your code for you!

=IF(C3&E3=K3&M3,3,IF(OR(AND(C3>E3,K3>M3),AND(C3<E3,K3<M3),AND(C3=E3,K3=M3,NOT(K3&M3=""))),1,IF(K3&M3="","-",0)))

[:-)]

This is why someone else should always check your code for you!

Glad I could be of assistance

Many many thanks for all your work.

All the best.