Oh it's too complicated to explain here!

Hi all,

Ok, I've made this far too complicated for myself. I'm keeping track of scores in a football prediction game where you get 3 points for an Away win, 2 points for a Home win, 1 point for a draw and 0 for a loss. I'm comparing a list of predictions against the actual results using an If AND function, something like this:

=IF(AND(M21="HW",M44="HW"),2,IF(AND(M21="D",M44="D"),1,IF(AND(M21="AW",M44="AW"),3,0)))

However, I've made a boo-boo in my code. When there is a Draw, both teams are getting 1 point which gives me twice the points. Now, I guess I could add another value like D2 or something like that and apply that to the away team in each drawn game, but that would make things a little more complex than I have already made them! So, the easiest solution for me is to simply halve the score of all the drawn games, but I can't figure out how to reference this in my code. I was thinking a COUNTIF function to get the amount of drawn games, and then using that result to halve the score of all the drawn matches, but this is proving harder than I thought it would be. Now, I'm not asking anyone to completely recode this for me, but has anyone got any ideas?

Just to reiterate, if there are four drawn games in one week, I should get 4 points for them, but at the moment it is giving me 8.

Thanks in advance

Joe

Hi Joe,

Without seeing a sample workbook it's going to be difficult to understand why you're doubling the value when your logic returns 1 is M21 + M44 are both D. I assume 21 and 44 are columns for prediction and result?

I can certainly resolve this, I just need to understand the table being used

Hi Dave,

Thanks for the reply but I have come up with a messy solution that works. I've done a count on the DRAWS and then divided by 2. Then I have taken that value from the sum total of my predictions, and it gives me the correct figure! Awfully messy, but I'm happy enough. Thanks