+ Reply to Thread
Results 1 to 12 of 12

Conditional outputs based on 2 values

  1. #1
    Registered User
    Join Date
    01-18-2012
    Location
    Swindon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Conditional outputs based on 2 values

    Hi,

    I am struggling with the number of embedded IF functions for what I am trying to achieve and would have assumed that there is a simpler way (there always is!)

    Basically here is a summary of what I would like to achieve.

    2 conditions, say A and B therefore 4 possible outcomes (AB, BA, AA, BB)

    Based on the outcomes, I would like that to determine how I add 4 other cells, say A1, A2, A3, A4

    IF AB, add A1,A2,A3
    IF BA, add A1,A2,A4
    IF AA, add A1,A2,A3,A4
    IF BB, add A1,A2

    How would i go about this?

    Thanks.
    Last edited by calador; 01-19-2012 at 09:52 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,370

    Re: Conditional outputs based on 2 values

    IF AB, add A1,A2,A3
    IF BA, add A1,A2,A4
    IF AA, add A1,A2,A3,A4
    IF BB, add A1,A2

    =IF(AND(A,B),A1+A2+A3,IF(AND(B,A),A1+A2+A4,IF(AND(A,A),A1+A2+A3+A4,IF(AND(B,B),A1+A2,""))))

    Can't quite see how A and B would be different to B and A but it's your question.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional outputs based on 2 values

    Hi and welcome to the forum.

    Maybe something like this??

    =IF(A1="AB";SUM(A1:A3);IF(A1="BA";SUM(A1+A2+A4);IF(A1="AA";SUM(A1:A4);IF(A1="BB";SUM(A1:A2);""))))

    Hope to helps you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    01-18-2012
    Location
    Swindon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional outputs based on 2 values

    Quote Originally Posted by TMShucks View Post
    IF AB, add A1,A2,A3
    IF BA, add A1,A2,A4
    IF AA, add A1,A2,A3,A4
    IF BB, add A1,A2

    =IF(AND(A,B),A1+A2+A3,IF(AND(B,A),A1+A2+A4,IF(AND(A,A),A1+A2+A3+A4,IF(AND(B,B),A1+A2,""))))

    Can't quite see how A and B would be different to B and A but it's your question.

    Regards, TMS
    Thanks for the super quick reply!

    Well I have simplified it, the variables are basically a "yes" and "no" answer to two questions therefore "yes" followed by "no" would be different to "no" followed by "yes" in this scenario.

    I think I have gotten as far as that formula but the part I am probably struggling to wrap my head around is the AND(A,B) section.

    As above, 2 questions,2 possible answers - yes or no. The formula will have to check the cells whether they are either yes's or no's.

    For example: the user will input B1 = yes, B2 = no.
    How would you adjust your formula to suit?

    =IF(AND(if(b1="yes",true,false),if(b2="no",true,false)),A1+A2+A3,..............)))))??

    Thought about arrays and whether they are relevant (talking about something I am not familiar with but think it may be relevant!)

    Much appreciated.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,370

    Re: Conditional outputs based on 2 values

    Change the A to B1= "yes" and B to B2="yes", for example

    I'm not sure which combinations are equivalent to yes, yes

    Yes, no
    No,yes
    Yes, yes
    No, no

    What sums are associated with each pair?
    Last edited by TMS; 01-18-2012 at 09:19 AM.

  6. #6
    Registered User
    Join Date
    01-18-2012
    Location
    Swindon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional outputs based on 2 values

    Solved!

    just out of curiosity, any other way around this without using that many embedded ifs.

    Thanks both of you.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,370

    Re: Conditional outputs based on 2 values

    You appear to be describing a hypothetical situation and, consequently, you get a hypothetical solution.

    With Excel, there are generally several ways to achieve a result, sometimes with a simple formula, sometimes with a complex one and sometimes with VBA.

    If you post a specific problem, backed up a sample workbook with some typical data you will very likely get a tailored solution to meet your needs.

    Regards
    Last edited by TMS; 01-18-2012 at 01:54 PM.

  8. #8
    Registered User
    Join Date
    01-18-2012
    Location
    Swindon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional outputs based on 2 values

    Well, if you teach a man how to fish, he will survive on his own. If you keep feeding him fish, he'll probably get sick of it - you get the drift

    I just thought that it would be a waste of your time to analyze a big set of data hence the simplified worded description.

    Anyway, I now understood (and fully appreciate!) your formula and am able to implement it in the workbook below. However, I was just wondering if there is another simple formula to achieve the same result without using VBA (know nothing about it). Otherwise, your previous answer is fully satisfactory; I will have a long day ahead of me trying to implement that on a larger set of data.

    Thanks again.
    Attached Files Attached Files

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,370

    Re: Conditional outputs based on 2 values

    I'm not sure about teaching you to fish. Now, my brother-in-law, he's been a fisherman all his life so, if you need help with that, I could maybe put you in touch

    With regard to your specific question, this is an alternative and shorter formula that seems to come up with the same results:

    =B9+(B6="yes")*B10+(B7="yes")*B11


    Regards, TMS

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,370

    Re: Conditional outputs based on 2 values

    Thanks for the rep.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  11. #11
    Registered User
    Join Date
    01-18-2012
    Location
    Swindon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Conditional outputs based on 2 values

    Last question on this topic.

    Which help file points to the syntax and usage of this for (B7="yes")*B11, where you can use brackets with * to substitute the IF function? Would like to read further on this.

    Thanks again for your prompt replies.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,370

    Re: Conditional outputs based on 2 values

    Not sure there's a Help for that ... just something you pick up along the way. A TRUE evaluates to 1 and a FALSE to 0 when multiplied. So, "yes"="yes" gives TRUE which is coerced to a 1 and multipled by the contents of B11.

    Regards, TMS

+ 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