Hi guys, this is a new type of post for me Formula Friday.  Every Friday I will blog on a formula or function in Excel.  I’m looking forward to this new posting schedule.  Today’s post will be the IF function in Excel.  IF is one of Excel’s LOGICAL functions.

Simply put the IF function checks to see if a condition you specify is true or false.

 

The Syntax for this function is

=IF(LOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE)  so you need to know at least  3 different arguments to write these logical arguments or functions.

The first argument is your logical test, the second is the value Excel will return if the logical test result if TRUE, the third argument is the value Excel will return if the logical test result is FALSE.

 

The VALUE_IF_TRUE and VALUE_IF_FALSE  values can be a cell reference or a formula or any text your specify.  If your TRUE or FALSE value/response  is to be  TEXT then the text values require them to be enclosed in quotations ” ”

Let’s look at the a simple example.

 

 

=IF(D2>10,”GREATER”,”LESS THAN”)

Excel returns LESS THAN because the value in cell D2 is less than 10.

 

 

 Let’s turn up the volume!

You can nest up to 64 IF functions in Excel…..if you were using that many I would suggest using one of Excel’s LOOKUP functions.  But, nested IF statement can be really useful.  Let’s take a look at a nested IF statement that will calculate sales commission for an employee based on their monthly sales automatically rather than having to calculate them individually.

 

The commission thresholds are as follows

up to 500 the commission is 5%

between 500 and 800 commission is 7%

between 800 and 1000 commission is 10%

over 1000 the commission is 15%

And here are the results of the commission applied to the value of sales.

 

Here is the formula in detail

=IF(C5<500,C5*5%,IF(C5<800,C5*7%,IF(C5<1000,C5*10%,C5*15%)))

The logical test in the first IF statement checks if the sales figure in C5 is less than 500. If it is, it calculates commission at 5% and stops at this point and enters the value of the commission. If it is not then it must be over 500 so moves to the next IF statement.

The logical test in the second IF statement checks if the sales figure in C5 is less than 800. We already know it must be more than 500 or we wouldn’t have got to this point. If it is less than 800, it calculates commission at 7%.   Otherwise it must be greater than or equal to 800 so we move to the next IF statement.

The logical test in the third and final IF statement checks if the sales figure in C5 is less than 1000. If it is, it calculates commission at 10%. Otherwise, it must be greater than or equal to 1000, so it calculates commission at 15%. At this point there are no more IF statements, no more logical tests we need to do, and we have our answer.

 

I hope you enjoyed this instalment of Formula Friday!