Formula help for different combinations of 3 multiple choice columns

[crai6m4ck]

Hello all! I am fairly new to Excel, and I'm running into an issue with a formula that is too difficult for me to figure out.

I am hoping that someone can assist me with a formula to handle three multiple choice columns.

Column 1: two choices, Buy or Sell
Column 2: two choices, Yes or No
Column 3: two choices, Yes or No

Depending on the combination of the first 3 columns, Column 4 will either be:
0
239.60
-239.60
299.50
-299.50

If Column 1 is "Sell", then column 3 is automatically "No", and Column 4 should be 0. (Column 2 won't matter/have any effect)
If Column 1 is "Buy", both Column 2 and 3 are "Yes", then Column 4 will be 299.50.
If Column 1 is "Buy", Column 2 is "Yes", and Column 3 is "No", then Column 4 will be -299.50.
If Column 1 is "Buy", Column 2 is "No", and Column 3 is "Yes", then Column 4 will be 239.60.
If Column 1 is "Buy", Column 2 is "No", and Column 3 is "No", then Column 4 will be -239.60.

Thanks in advance!

[oeldere]

Welcome on the excel forum.

Not sure I can help, but the text below definitly helps other forummembers (to help you).


HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[crai6m4ck]

Book1.xlsx

Something like this?

[JeteMc]

This formula replicates the results in D2 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[crai6m4ck]

This formula replicates the results in D2 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

This is exactly what I was looking for! Thanks!

Would you be willing to explain how the formula actually works? I'm a novice that would like to understand it.

Sorry if this is confusing, but this is the way I read it:
First equation: If A2 equals "sell" then the result is 0. Otherwise, if A2 does not equal "sell" then look to see if B2 equals "yes". If so, the result is 299.5, but if B2 does not equal "yes", then the result is 239.5.
Second equation: Now, if C2 equals "yes" multiply the result of the first equation by 1, but if it doesn't equal "yes" then multiply the result of the first equation by -1

Is this correct?

[JeteMc]

Yes, that is correct and isn't confusing at all to me.