What’s conditional analysis? In its primary meaning, when Excel performs different actions depending on whether a condition is met, this is a conditional analysis.
Excel provides some built-in functions for doing conditional calculation; these functions are the basis for doing complex condition calculations.
I will show you how to use some functions to do conditional analysis in this post.
Simple Condition with IF
IF is the simplest and the most basic conditional function in Excel. It has three arguments: the condition, what to do if the state is true, and what to do if the condition is false.
The figure below displays a list of students and their scores. For each student, say that you want to determine whether his/her score is above or below average for all the students. You report “above” for higher-than-average scores, and for lower-than-average scores, “Below.” This is the formula used in this example:
=IF(B2>AVERAGE(B$2:B$11),”High”,”Low”)
In this formula, the reference to B2 is relative to rows and will change as the formula is copied to different cells. The B$2:B$11 is absolute to rows.
Multiple Conditions with Nested IF
A complex conditional analysis can be made by string simple condition together, this is also called nested condition functions. The value_if_true and value_if_false can contain simple condition of their own. This allows you to test more than one condition where subsequent are dependent on the first one.
The figure below shows a spreadsheet with two user input fields for the countries and cities. The properties are listed in two ranges below the user input fields. For this example, when the user select the Country and a City of North America, you want a formula to report whether the city is the capital of the country or not. Here is the formula:
=IF(A3=”USA”,IF(B3=”Washington”,”YES”,”NO”),IF(B3=”Ottawa”,”YES”,”NO”))
With some conditional analysis, the result of the first condition causes the second condition to change, In this case, if the first condition is USA, the second condition is Washington or New York. If the first condition is Canada, the second condition changes either Ottawa or Montreal, The data validation changes to allow only the appropriate choices based on the first condition.
Here is trick that many people don’t know how to resolve. How to do auto city population when a country field changed. You only allow the cities in that country displayed on City field, in this example, it’s B3.
The solution is simple, using the Indirect Function within Data Validation. We set the range of the city list with the Country name in A3. When A3 changes, the data validation also changes to the right city list.
