I am filling in a players FOR and AGAINST goals prediction for a soccer league in columns C and D respectively. Am putting in the actual FOR and AGAINST score in columns G and H. I want the outcome to be in column Q following the rules 1 pt for correct FOR score OR 1 pt for correct AGAINST score, OR 3 pts for both being correct.
So for a 1-1 result, a 2-1 predicted score would be 1 pt, a 1-3 loss would be 1 pt, a 1-1 draw would be 3 pts.
I am sure it is something simple but I cant get the IF command to output with 3 variables, being so idiotic at excel.
Please find the worksheet I am building attached.
As an aside, I have the formula =IF(E23=I23,3,IF(E23=J23,1,IF(E23=K23,1,IF(E23=L23,1,IF(E23=M23,1,IF(E23=N23,1,IF(E23=O23,1,0))))))) in column R to add pts for correctly guessing the 1st or other scorer from our team (3pts for 1st scorer, 1 pt if he scores anytime) but it adds a point if a player misses a week when column E is empty. Is there anyway around this?
Thanks for any advice.
Predictions Worksheet.xlsx
Bookmarks