Excel - World Cup spreadsheet formula problem

[MarcLodge]

Hi all,
I have put together a World Cup spreadsheet which I can't get to work 100% correctly and I'm struggling to sort it out.

I have two columns which are the actual result of the match, plus two columns which are the users guess. What I want to do is compare the result against prediction and award points as follows:
1 point for getting the result correct, plus 1 point for getting each team's score correct. This gives the possibility of 0,1,2, or 3 points.

I've written an if statement on the cell that gives the points, which works fine for 0, 1, or 3 points, but I can't seem to work out how to get 2 points in there. I may have over complicated it in the way I've approached it, but would be grateful for some advice. The code follows and I've formatted it to make it more readable. I know I need to change the OR statement but when I tried, it told me I had gone to too many levels of embedded-ness.

=(IF(AND($C7=" ",$E7=" "),0,
IF(AND($C7=F7,$E7=H7),3,
IF(OR($C7=F7,$E7=H7),1,
IF(AND($C7>$E7,F7>H7),1,
IF(AND($C7=$E7,F7=H7),1,
IF(AND($C7<$E7,F7<H7),1,
0)))))))

Any help gratefully received.

Marc

 

[SkipVought]

Hi,

Please post a REPRESENTATIVE sample of data from column C, E, F & H, the LOGIC amd the expected results for each example set.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MarcLodge]

Skip,
I'm pleased to say I managed to work it out myself. If anybody is interested, I'll post my code and answer your question as follows:

C-E, F-H / points
1-0 1-0 / 3 (all correct)
1-0 0-0 / 1 (wrong result, but one team's score correct)
1-0 0-1 / 1 (wrong result, but one team's score correct)
1-0 2-1 / 1 (correct result, but both scores wrong)
1-0 2-0 / 2 (correct result, one team's score correct)

The code I have used is:

=(IF(AND($C7=" ",$E7=" "),0,
IF(AND($C7=F7,$E7=H7),3,
IF(AND($C7>$E7,F7>H7),IF(OR($C7=F7,$E7=H7),2,1),
IF(AND($C7=$E7,F7=H7),IF(OR($C7=F7,$E7=H7),2,1),
IF(AND($C7<$E7,F7<H7),IF(OR($C7=F7,$E7=H7),2,1),
IF(OR($C7=F7,$E7=H7),1,0)))))))

Probably not the best or most elegant way of doing it, but it appears to work.

Marc

 

[SkipVought]

Please remember, no one knows what is in your head, unless you make it known in writing.

What do each of these columns of data represent?

I could GUESS, but I'd rather not, as I am not getting points for clarvoyancy.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MarcLodge]

Sorry Skip I thought I'd made myself clear,

What do each of these columns of data represent?

I have two columns which are the actual result of the match, plus two columns which are the users guess. What I want to do is compare the result against prediction and award points as follows:
1 point for getting the result correct, plus 1 point for getting each team's score correct. This gives the possibility of 0,1,2, or 3 points.

Anyway, as mentioned in my previous post, I managed to resolve the problem myself (always the best way), so there's not need to guess. Thanks for your input though.

Marc