Formula not working out correctly

I have a formula in which for all intensive purporses was going great.........

Until I ran into a slight problem.

As I have posted in other threads trying to get help (though I worked it out on my own in the end), I am doing a soccer prediction league and wanted to run things smoothly.

Here is my formula:

Please Login or Register  to view this content.

I realise its a lot of ifs and ands but it was doing the job.

formula help.png

Above is a copy of the spreadsheet. In column G I write the scores manually as 2-1 etc, but in column H and I I use =IF(G3="","",LEFT(G3,FIND("-", G3)-1)) to seperate them (use right for I).

The above IF and AND formula is in column J to calculate the points earned.

My issue is, the game finished 0-0, and all the ones with 1 points are correct as although they predicted the wrong outcome, they at least got 1 teams amount of goals correct. However the players that got 3 points are clearly wrong. They didn't predict a draw nor did they get a teams goals right.
If I changed one of the teams results to 2-0, then they are changed to 1 point. If I change the actual score to 1-1 (G2) instead of 0-0, the all the scoring wins still get 3 points. Only seems to occur to games where both teams score, and a win is determined.

I'm sorry if I don't make any sense, but this is really bugging me now.

Column J is where I work out my points.

The long formula with the IFS and ANDS goes into J3 in which it then works out whether a participant gets 5 points for predicting the entire result correctly (so if game finished 3-0 and they predict 3-0). They get 4 points if they get the score correct, but only 1 teams goals (so again, predicted 3-0 but game finished 3-1). 3 Points for correct result (3-0 prediction, 2-1 result). 1 point for a losing prediction BUT participant guesses correctly 1 teams goal count.

Hopefully the above paragraph answers both your questions.

Here is the formula again, in quote brackets instead of code.

=IF(I3="","",IF(AND($H$2=H3,$I$2=I3)=1,5,IF(AND($H$2>H3,$I$2>I3,$H$2=H3)=1,4,IF(AND($H$2<H3,$I$2<I3,$H$2=H3)=1,4,IF(AND($H$2>H3,$I$2>I3,$I$2=I3)=1,4,IF(AND($H$2<H3,$I$2<I3,$I$2=I3)=1,4,IF(AND($H$2>H3,$I$2>I3)=1,3,IF(AND($H$2<H3,$I$2<I3)=1,3,IF(AND($H$2=$I$2,H3=I3)=1,3,IF(AND($H$2>H3,$I$2<I3,$H$2=H3)=1,1,IF(AND($H$2<H3,$I$2>I3,$H$2=H3)=1,1,IF(AND($H$2>H3,$I$2<I3,$I$2=I3)=1,1,IF(AND($H$2<H3,$I$2>I3,$I$2=I3)=1,1,IF(AND($H$2>$I$2,H3=I3,$H$2=H3)=1,1,IF(AND($H$2<$I$2,H3=I3,$H$2=H3)=1,1,IF(AND($H$2>$I$2,H3=I3,$I$2=I3)=1,1,IF(AND($H$2<$I$2,I3=H3,$H$2=H3)=1,1,IF(AND($H$2=$I$2,H3>I3,$H$2=H3)=1,1,IF(AND($H$2=$I$2,H3<I3,$H$2=H3)=1,1,IF(AND($H$2=$I$2,H3>I3,$I$2=I3)=1,1,IF(AND($H$2=$I$2,I3<H3,$H$2=H3)=1,1,0)))))))))))))))))

Originally Posted by nflsales

AND($H$2>H3,$I$2>I3,$H$2=H3)
AND($H$2<H3,$I$2<I3,$H$2=H3)
The colored ones is not possible - if h2>h3 then how h2=h3 will be
all the and functions will not work straightaway it give false in if condition

I think I have found exactly where I went wrong with your help.

Tiredness kicking in and making simple mistakes.

Originally Posted by FDibbins

That part looks wrong...

I dont think the =1 should be there. That needs to read IF H2=H3 AND I2=I3, then do "this", ELSE do "that". You cannot have an AND() = something, it will either return a TRUE or a FALSE.

I did not look further into your formula, because I see that same error repeated (although Siva has picked up another problem)

Originally Posted by FDibbins

Perhaps you missed post #4? (you posted almost when I did)

I have removed the =1.

I have also taken on board what Siva wrote and hopefully I've corrected the issue.

Thank you guys.

Done it without issues.

Removed the =1, and sorted some of the cells to where they should be on the formula and it worked a treat.

Thanks..

Here is the new formula.

Please Login or Register  to view this content.

Originally Posted by FDibbins

I was just looking at your formula again, after trying to clean it up a bit, and it seems you repeat the same condition a few times. Take a look at just the 1st few IF's...
IF(AND($H$2=H3,$I$2=I3),5,
IF(AND($H$2>H3,$I$2>I3),4,
IF(AND($H$2<H3,$I$2<I3),4,
IF(AND($H$2>H3,$I$2>I3),4,
IF(AND($H$2<H3,$I$2<I3),4,
IF(AND($H$2>H3,$I$2>I3),3,
IF(AND($H$2<H3,$I$2<I3),3,

Im sure there are many more like this

I had the cells in the wrong place, thats where I messed up. Hopefully the updated one makes more sense.
I wish I could tidy it up more but I wouldn't know where to start, and it hopefully is now working like a dream.