# Conditional outputs based on 2 values

1. ## 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

Thanks.  Register To Reply

2. ## Re: Conditional outputs based on 2 values

=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  Register To Reply

3. ## 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.  Register To Reply

4. ## Re: Conditional outputs based on 2 values Originally Posted by TMShucks =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.

=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.  Register To Reply

5. ## 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?  Register To Reply

6. ## 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.  Register To Reply

7. ## 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  Register To Reply

8. ## 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.  Register To Reply

9. ## 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  Register To Reply

10. ## Re: Conditional outputs based on 2 values

Thanks for the rep.

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

11. ## 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.  Register To Reply

12. ## 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  Register To Reply