How to use MS Excel's IF function

In his second post guiding you through useful Microsoft Excel tools for accountants, AAT student Muhammad Anisur Rahman shows you how to use the IF function

As a Finance MI analyst, to analyse and prepare financial reports, I use Excel’s IF function every day at work. It is one of the most powerful built-in function in Excel which can be used to make decision based on the outcome.

For example, we have 10 customers and we have to calculate bulk discount (Bulk Discount: a lower price that is offered to customers for buying a large number or amount of goods) based on following conditions.

• Sales amount over £100,000 5% bulk discount
• Sales amount less than £100,000 1% bulk discount

In order to calculate the bulk discount we have to use the IF Function which follows the following structure:

=IF(logical_test, value_if_true,value_if_false)

logical_test
First argument logical_test: to check if sales amount is greater £100,000

Sales amount > £100,000
(> greater than symbol)

value_if_true
Second argument value_if_true: if sales amount > £100,000 then calculate 5% bulk discount

Sales amount * 0.05
(Excel use the asterisk (*) symbol, one above the number 8 on keyboard, press Shift+8)

value_if_false
Third argument value_if_false: If sales amount < £100,000 then calculate 1% bulk discount

(> less than symbol)

Sales amount * 0.01

For the purpose of above example, create a table of data as per image shown below:

Step 1: Create sample data table

Now, we will type the IF function in cell D2 to calculate bulk discount for XYZ Ltd.

Select D2 and type the formula =IF(C2>100000,C2*0.05,C2*0.01)

Step 2: Type IF function in cell D2

This means, if ‘Sales Amount’ in cell C2 is greater than £100,000 multiply it by 0.05 to give 5% discount, otherwise multiply it by 0.01 to give 1% discount. In this case, the amount in cell C2 is £50,000 which is less than £100,000. So, the 1% bulk discount of £500 will be calculated in cell D2 as per our IF formula.

Step 3: 1% bulk discount

To calculate the discount for other companies, we will copy the formula from cell D2 to D3:D11.  Please check my previous blog on the SUM formula to see how to copy formula from one cell to another cell.

Select cell D2 and press Ctrl + C to copy the formula.

Select range D3 to D11 and hit Enter to paste the formula.

Step 4: Copy formula from D2

Now, if you carefully check the formula in cell D7, you will notice that (110,000 x 0.05 = 5,500) 5% bulk discount is given to customer Asha Ltd. This is because the ‘Sales Amount’ is greater than 100,000.

Step 5: 5% bulk discount applied

You can also watch an online video demonstration of this example to help you if needed:

In the next part of my blog I will demonstrate the VLookUp. If you have any suggestions or question regarding this example, please feel free to leave your comment here or contact me direct.

Muhammad Anisur Rahman has qualified with AAT.