Solved

If Statement with Multiple conditions

Posted on 2016-08-25
Medium Priority
59 Views
Hi Experts

I have a situation where I need to create an if statement with lots of conditions.  I am just not sure
where to start or if this is even the best way to tackle my task.

I have two columns A and B.  In each cell is a value A,B,C OR D.  In column C I need to create an answer to the following question.

If column A is A and Column B is A then the answer would be "No Change"
If column A is A and Column B is B then the answer would be "Degraded"
If column A is A and Column B is C then the answer would be "Degraded"
If column A is A and Column B is D then the answer would be "Degraded"
If Column A is B and Column B is A then the answer would be "Improved"
If Column A is B and Column B is B then the answer would be "No Change"
If Column A is B and Column B is C then the answer would be "Degraded"
If Column A is B and Column B is D then the answer would be "Degraded"
If Column A is C and Column B is A then the answer would be "Improved"
If Column A is C and Column B is B then the answer would be "Improved"
If Column A is C and Column B is C then the answer would be "No Change"
If Column A is C and Column B is D then the answer would be "Degraded"
If Column A is D and Column B is A then the answer would be "Improved"
If Column A is D and Column B is B then the answer would be "Improved"
If Column A is D and Column B is C then the answer would be "Improved"
If Column A is D and Column B is D then the answer would be "No Change"

Any Help would be greatly appreciated.

Andy
0
Question by:spudmcc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 8

Expert Comment

ID: 41770850
try this
``````=IF(OR(AND(A1="A",B1="B"),AND(A1="A",B1="C"),AND(A1="A",B1="D"),AND(A1="B",B1="C"),AND(A1="B",B1="D"),AND(A1="C",B1="D")),"Degraded",IF(OR(AND(A1="B",B1="A"),AND(A1="C",B1="A"),AND(A1="C",B1="B"),AND(A1="D",B1="A"),AND(A1="D",B1="B"),AND(A1="D",B1="C")),"Improved",IF(OR(AND(A1="A",B1="A"),AND(A1="B",B1="B"),AND(A1="C",B1="C"),AND(A1="D",B1="D")),"No Change","")))
``````
0

LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 41770900
You can test text strings or letters by sort order and produce a simpler formula:

If test values are in A2 and B2 then use:

Regards,
Glenn
EE_Q_28965694.xlsx
2

LVL 32

Expert Comment

ID: 41770908
@Glenn
Very smart indeed. Good one. :)
0

Expert Comment

ID: 41770911
I took a simpler approach.  I sorted the information and saw that these are like grades.  The following formula works because the value of A < B < C < D.

0

LVL 27

Expert Comment

ID: 41770915
Thanks.

Text comparisons like this are not case-sensitive, which is a good feature.  There is no error checking in my example and that might be a useful modification (ex. if either column A or B values are blank, another answer should result)
0

Author Closing Comment

ID: 41770957
Thank you to all of the experts that contributed.  I am using the solution provided by Glenn because it is simple, straight forward and for me it works perfectly.

Again, thank you so much for sharing your knowledge and time with us "non-experts".
0

LVL 27

Expert Comment

ID: 41770962
You are most welcome.  I always like finding simple solutions like this!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Suggested Courses
Course of the Month10 days, 17 hours left to enroll