Excel Conditional Logic - Part 1

 

Excel has a number of logical functions which allows you to set various "conditions" and have data respond to them. For example, you may only want a certain calculation performed or piece of text displayed if certain conditions are met. To start using Functions in Excel you need to

Open Excel => Click in a cell => Click on Insert Menu button =>  Function 

 

Lets consider a simple example. When we are in a restaurant and have X amount of money, we compare that the dish we select costs less than or equal to the amount we have (in this case X amount). Your selection is based on the condition that the price of the dish should be less than X amount. We can do similar calculations in Excel.  

IF STATEMENT
The IF function is used to analyze data, test whether or not it meets certain conditions and then act upon its decision

IF statement is accompanied by three arguments enclosed in one set of parentheses; the condition to be met (logical_test); the action to be performed if that condition is true (value_if_true); the action to be performed if false (value_if_false). Each of these is separated by a comma, as shown;
=IF ( logical_test, value_if_true, value_if_false)

For your example above we will do the following calculation
= IF ( X >= DISH_PRICE , BUY , DON'T BUY )

In the Logical test : X >= DISH_PRICE, we are saying that if X (amount we have) is greater than or equal to DISH_Price, then 'BUY'; if not 'DON'T BUY'

Note: We can also use the following symbols if we want to do other comparizons
> Greater than
< Less than
> = Greater than or equal to
< = Less than or equal to
= Equal to
< > Not equal to

NESTED IF
When you need to have more than one condition and more than two possible outcomes, a NESTED IF is
required. This is based on the same principle as a normal IF statement, but involves "nesting" a secondary
formula inside the main one. The secondary IF forms the FALSE part of the main statement, as follows;
=IF(1st logic test , 1st true value , IF(2nd logic test , 2nd true value , false value))

Only if both logic tests are found to be false will the false value be returned. Notice that there are two sets
of parentheses, as there are two separate IF statements. This process can be enlarged to include more
conditions and more eventualities ‐ up to seven IF's can be nested within the main statement. However,
care must be taken to ensure that the correct number of parentheses are added.

 

 

In the example, sales staff could now receive one of three possible ratings;
=IF(B2>1000,"GOOD",IF(B2<600,"POOR","AVERAGE"))

To make the above IF statement more flexible, the logical tests could be amended to measure sales against
cell references instead of figures. In the example, column E has been used to hold the upper and lower
sales thresholds.

=IF(B2>$E$2,"GOOD",IF(B2<$E$3,"POOR","AVERAGE"))
(If the IF statement is to be copied later, this cell reference should be absolute).

We have other conditional statements 'AND, OR, NOT' that we can use in Excel. These are covered in Excel Conditional Logic - Part 2