- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Need Help with an Excel Formula
Posted on 12/15/14 at 2:43 pm
Posted on 12/15/14 at 2:43 pm
What formula do I need to use for the following scenario?
Lets say column A contains 2 possible outcomes (A or B) and column B contains 3 other outcomes (Y or Z), and dependent on the combination the result may be good (+) or bad (-) for my purposes. A,B,Y, and Z are text not numerals.
If A1 contains "A" and B1 contains "Y" then C1 is +
If A1 contains "A" and B1 contains "Z" then C1 is -
The inverse is also true.
If A1 contains "B" and B1 contains "Y" then C1 is -
If A1 contains "B" and B1 contains "Z" then C1 is +
I assume is a combination of using the if/then/and formulas but I haven't yielded the results I am looking for.
Thanks!
Lets say column A contains 2 possible outcomes (A or B) and column B contains 3 other outcomes (Y or Z), and dependent on the combination the result may be good (+) or bad (-) for my purposes. A,B,Y, and Z are text not numerals.
If A1 contains "A" and B1 contains "Y" then C1 is +
If A1 contains "A" and B1 contains "Z" then C1 is -
The inverse is also true.
If A1 contains "B" and B1 contains "Y" then C1 is -
If A1 contains "B" and B1 contains "Z" then C1 is +
I assume is a combination of using the if/then/and formulas but I haven't yielded the results I am looking for.
Thanks!
5
Posted on 12/15/14 at 2:56 pm to northern
Try this in c1:
=IF(OR(A1&B1="AY",A1&B1="BZ"),1,0)
=IF(OR(A1&B1="AY",A1&B1="BZ"),1,0)
Posted on 12/15/14 at 3:03 pm to northern
Edit: nvm, simpler formula found.
This post was edited on 12/15/14 at 3:04 pm
Posted on 12/15/14 at 3:05 pm to Brisketeer
quote:
Try this in c1:
All results in C1 are showing as 1 unless they are blank the are showing 0. I'll play with it a bit more.
Thanks.
Posted on 12/15/14 at 3:09 pm to northern
quote:
All results in C1 are showing as 1 unless they are blank the are showing 0. I'll play with it a bit more.
That one worked well for me. I could see how it could cause issues if you are searching for text that contains something though. Here's a little bit longer version that also works. His formula is a lot cleaner though.
=IF(AND(A1="A",B1="Y"),"+",IF(AND(A1="A",B1="Z"),"-",IF(AND(A1="B",B1="Y"),"-","+")))
Posted on 12/15/14 at 4:10 pm to northern
I use conditional formating in these situations because it makes the spreadsheet look Faaaabulous!
Posted on 12/15/14 at 4:20 pm to TDawg1313
quote:
=IF(AND(A1="A",B1="Y"),"+",IF(AND(A1="A",B1="Z"),"-",IF(AND(A1="B",B1="Y"),"-","+")))
This worked. Thanks!
Posted on 12/15/14 at 7:02 pm to northern
quote:
=IF(AND(A1="A",B1="Y"),"+",IF(AND(A1="A",B1="Z"),"-",IF(AND(A1="B",B1="Y"),"-","+")))
Is that English?
Posted on 12/15/14 at 7:19 pm to northern
quote:
3 other outcomes (Y or Z)
Posted on 12/15/14 at 9:51 pm to northern
Are you limited in columns you could use? Do you want the same result if "+" and same if "-"?
If so, build a hidden column next to A/B and Y/Z, and use a singe IF to assign the value of X as "1" and Y as "-1", same for A/B. Then just use IF that Column A/B x Column Y/Z is positive/negative.
If so, build a hidden column next to A/B and Y/Z, and use a singe IF to assign the value of X as "1" and Y as "-1", same for A/B. Then just use IF that Column A/B x Column Y/Z is positive/negative.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News