# IF Function multiple possible outcomes?

1. ## IF Function multiple possible outcomes?

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?

Predictions Worksheet.xlsx  Register To Reply

2. ## Re: IF Function multiple possible outcomes?

Hi there, this is the formula that will get the right value

=IF(AND(C5=G5,D5=H5),3,IF(OR(C5=G5,D5=H5),1,0))

Please make sure that all values are numbers as I noticed that some entries were TEXT. The way to test this is to declare the entire colum as number.  Register To Reply

3. ## Re: IF Function multiple possible outcomes?

The second problem can be solved by testing column E for non-blanks IF (c5<>"",....your scoring if formulas,0)  Register To Reply

4. ## Re: IF Function multiple possible outcomes? Originally Posted by rcm Hi there, this is the formula that will get the right value

=IF(AND(C5=G5,D5=H5),3,IF(OR(C5=G5,D5=H5),1,0))

Please make sure that all values are numbers as I noticed that some entries were TEXT. The way to test this is to declare the entire colum as number.
Thanks very much for getting back to me with the suggestions.

The formula I entered but returned all "0" except for 2 lines where it returned 1 for the Score Against columns. It ignored when Score For was correct? What is weird is that if I then go and edit the source data to the same value as it currently is, the formula correctly changes??

I will upload my worksheet with your formula in it, and you can see.

http://speedy.sh/yafzx/MatchPredictions-Worksheet.xlsx

Which entries are text? Do you mean the first few rows? Sorry, am not the brightest when it comes to excel.

For the non blanks formula amendment I used =IF(E7<>"",IF(E7=I7,3,IF(E7=J7,1,IF(E7=K7,1,IF(E7=L7,1,IF(E7=M7,1,IF(E7=N7,1,IF(E7=O7,1,0)))))))) and it returned FALSE with the blank rows. I know this is because there is a ',0' missing somewhere but Im not sure where?

Sorry again, Im taking tentative baby steps here :S  Register To Reply

5. ## Re: IF Function multiple possible outcomes?

The ***** eye cannot see the difference at first.

What I did since I had the same problem I declared all score columns as numbers. I placed the cursor at the top of the column(s), where the letter identifying it is, selected and declared them as "number" instead of "general" by selecting from the data types combobox. just then one can see if the entries are text or numbers because the entries that are numbers will have 2 decimals.  Register To Reply

6. ## Re: IF Function multiple possible outcomes?

Got it working now, thanks very much for your help rcm, really helped.   Register To Reply

7. ## Re: IF Function multiple possible outcomes?

Helped me as well thank you to all

JP   Register To Reply