Solved

If Statement with Multiple conditions

Posted on 2016-08-25
7
42 Views
Last Modified: 2016-08-25
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
Comment
Question by:spudmcc
7 Comments
 
LVL 8

Expert Comment

by:itjockey
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","")))

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 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:
=IF(A2=B2,"No Change",IF(A2<B2,"Degraded","Improved"))

See also example file attached.

Regards,
Glenn
EE_Q_28965694.xlsx
2
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41770908
@Glenn
Very smart indeed. Good one. :)
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:morebeus
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.

=IF(A1=B1,"No Change", IF(A1<B1,"Degraded","Improved"))
0
 
LVL 27

Expert Comment

by:Glenn Ray
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

by:spudmcc
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

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now