+ Reply to Thread
Results 1 to 13 of 13

Nested IF statements or CHOOSE statements needed for multiple conditions formula

  1. #1
    Registered User
    Join Date
    05-14-2009
    Location
    london,England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Nested IF statements or CHOOSE statements needed for multiple conditions formula

    I have the text in two column - example column R has "High" and column s has "annual".

    I have a set of conditions that I need the formula to perform:

    if Column R has "high" and column S has "annual", the formula should display the words "1" in column U.

    if Column R has "medium" and column S has "annual", the formula should display the words "1" in column U.

    if Column R has "low" and column S has "monthly", the formula should display the words "1" in column U.

    if Column R has "high" and column S has "quarterly", the formula should display the words "3" in column U.

    if Column R has "medium" and column S has "quarterly", the formula should display the words "2" in column U.

    if Column R has "low" and column S has "quarterly", the formula should display the words "1" in column U.


    Can someone please help in the next few minutes if possible, I'd appreciate it!

    Thanks!!
    Last edited by salmansaif; 05-14-2009 at 03:22 PM. Reason: title

  2. #2
    Registered User
    Join Date
    05-14-2009
    Location
    london,England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Quick formula help- should be easy for the techies!!

    BTW, I am on Excel 2007, if that matters. Thanks.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Quick formula help- should be easy for the techies!!

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

  4. #4
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    I used some shorthand for the formula (H for high, M for Medium, etc.) but you get the picture...

    Please Login or Register  to view this content.
    I've left blanks ("") for the conditions you did not define (like R1=Highand S1=Monthly).

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    Thank you for revising your title salmansaif - good titles aid searches.

    Welcome to the Board.

  6. #6
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    U can use the long way by IF functionusing formula

    =IF(OR(AND(R1="high",S1="annual"),AND(R1="medium",S1="annual"),AND(R1="low",S1="quarterly"),AND(R1="low",S1="monthly")),1,IF(AND(R1="high",S1="quarterly"),3,2))

    Or create a small table near and use SUMPRODUCT

    =SUMPRODUCT((R1=N2:N7)*(S1=O2:O7)*(P2:P7))


    In short see attached file
    Attached Files Attached Files
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  7. #7
    Registered User
    Join Date
    05-14-2009
    Location
    london,England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    Thanks for all your help but for some reason, the formula as I put it is not erroring out, but still giving the wrong answer.

    I am attaching the spreadsheet - the formula needs to be in column "U" based on the blue "key" columns.

    So for example, if R7 is medium and S7 is monthly, based on the key, the formula should generate "3" in column U.

    Please feel free to edit the spreadsheet and attach back to your response. Again, thanks for your fast response!
    Attached Files Attached Files
    Last edited by salmansaif; 05-14-2009 at 04:03 PM.

  8. #8
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    salmansaif,

    If you take a look at ContaminatedWitExcel's attachment you will see that the results you have match his IF statement (Medium + Monthly = 2).

    My origininal IF statement will return a blank since that is not a condition you specified. Since it looks like you will have "other" conditions I would not use either IF statement. I recommend you follow ContaminatedWitExcel's advice and use a lookup table, however I would use INDEX and MATCH instead.

    See my modifications of his file (I did not use yours except to verify the IF statement was correct).
    Attached Files Attached Files

  9. #9
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    I think u should provide us with all conditions...

    In any case I've done some changes by adding new sheet, which u can hide in the future.

    See book pls..

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    well they wouldnt medium/monthly is not in your original list of requirements
    but the table with
    =SUMPRODUCT((R1=N2:N7)*(S1=O2:O7)*(P2:P7))
    idea is worth persuing then you can have them all
    Last edited by martindwilson; 05-14-2009 at 04:32 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    Chance2 created better table than me.

    I used this table in source sheet. It'll easy for editing
    Formula is:

    =IF(ISERROR(OFFSET('Source sheet'!$A$1,MATCH(KY!R7,'Source sheet'!$A$2:$A$4,0),MATCH(KY!S7,'Source sheet'!$B$1:$D$1,0))),"",OFFSET('Source sheet'!$A$1,MATCH(KY!R7,'Source sheet'!$A$2:$A$4,0),MATCH(KY!S7,'Source sheet'!$B$1:$D$1,0)))
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-14-2009
    Location
    london,England
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    thank u all so much!

  13. #13
    Registered User
    Join Date
    11-14-2008
    Location
    Tampa
    MS-Off Ver
    Professional Plus 2007
    Posts
    30

    Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

    If you want to get away from nested ifs, try using a lookup table with all your conditions and in column T, use vlookup to generate the value. I've included an attachement to show you what I did. What's nice about using a lookup table is you can easily add or modify it without modify the formula in column T.
    Attached Files Attached Files

+ 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