+ Reply to Thread
Results 1 to 7 of 7

IF Function multiple possible outcomes?

  1. #1
    Registered User
    Join Date
    11-24-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    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?

    Thanks for any advice.

    Predictions Worksheet.xlsx

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    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.

  3. #3
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    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)

  4. #4
    Registered User
    Join Date
    11-24-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Unhappy Re: IF Function multiple possible outcomes?

    Quote Originally Posted by rcm View Post
    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
    Last edited by Nomadski; 12-03-2012 at 03:12 PM.

  5. #5
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    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.

  6. #6
    Registered User
    Join Date
    11-24-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: IF Function multiple possible outcomes?

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

  7. #7
    Registered User
    Join Date
    02-11-2013
    Location
    Sydney Australia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: IF Function multiple possible outcomes?

    Helped me as well thank you to all

    JP

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1