Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Quick Excel Question (Nested IF's)

Options
  • 23-05-2010 5:32pm
    #1
    LightningBolt
    Registered Users Posts: 1,639 ✭✭✭


    I'm using a nested IF statement that reads as if cell 1 matches cell 2 then return a certain value based on what is contained within cell 2. So basically I predict the outcome of a match, I then enter in the actual outcome and want a separate sheet that will compare results, if they match I want to return either a 1,2 or 3 depending on what I've predicted and if they don't match I want to return a 0.

    I've written it as follows:
    =IF(Predictions!$I8=Predictions!H8, (IF(Predictions!I8="F", (1), (IF(Predictions!I8="D", (2), (IF(Predictions!I8="O", (3), (0))))))))
    Can someone point out why the IF statement is returning a false value if the first argument is not satisfied? I thought I could nest all the IF statements and then put the false value in at the end i.e. (0)

    Can anybody help? Let me know if further clarification is needed.


Comments

  • Options
    #2
    mid
    Closed Accounts Posts: 146 ✭✭mid


    LightningBolt wrote: »
    I'm using a nested IF statement that reads as if cell 1 matches cell 2 then return a certain value based on what is contained within cell 2. So basically I predict the outcome of a match, I then enter in the actual outcome and want a separate sheet that will compare results, if they match I want to return either a 1,2 or 3 depending on what I've predicted and if they don't match I want to return a 0.

    I've written it as follows:
    =IF(Predictions!$I8=Predictions!H8, (IF(Predictions!I8="F", (1), (IF(Predictions!I8="D", (2), (IF(Predictions!I8="O", (3), (0))))))))
    Can someone point out why the IF statement is returning a false value if the first argument is not satisfied? I thought I could nest all the IF statements and then put the false value in at the end i.e. (0)

    Can anybody help? Let me know if further clarification is needed.

    try this:

    =IF(Predictions!$I8=Predictions!H8, (IF(Predictions!I8="F", (1),  (IF(Predictions!I8="D", (2), (IF(Predictions!I8="O", (3),  (0))))))),(0))

    The first zero towards the end of the formula is used if I8 = H8, but not equal to F, D or O.

    The last zero in the formula is used if I8 and H8 are different.


  • Options
    #3
    LightningBolt
    Registered Users Posts: 1,639 ✭✭✭LightningBolt


    You're a hero! Such a simple solution:) Many thanks for that.


Advertisement