All courses available in-class or via remote login

The Most Useful Function in Excel

The Most Useful Function in Excel

May 23, 2017

The IF Function in Excel

The most common function in Excel would most likely be the SUM function, but our vote is that the excel IF function is definitely the most useful.  This tutorial explains how to use the IF function and how this can become an integral part to simplifying your formula editing.

‘IF’ is a logical function that allows for two (or more) possible outcomes. It is used to check the condition of a returned value and provide one value for a TRUE condition and another value for a FALSE condition.

Let’s have a look at a basic Excel IF function in operation:

Displayed in the example below, is the monthly sales figures of 9 sale agents working at Alpheius Global Enterprises.  They were given a target of $34,000 and anyone who has achieved that target will be given a commission of 5% on their sales.


In this example, we can use the IF Function to see which employees have reached their monthly sales targets.

If the IF function is TRUE then those employees who have met their monthly sales targets will be awarded 5% commission.

However, if the argument is FALSE, they have not reached their sales targets and will receive 0% commission.

  1. To calculate, click in the cell D7 where we want our function to calculate commission of our first employee.
  2. Then click on Insert Function command in the Formula Tab and find ‘IF’ Function. Click OK.

excel exert

 

3. After you’ve clicked OK, the IF Function dialog box should open with three arguments. Let’s plug these arguments in one by one.

Building the IF Function statement

excel exert

1. Click in the first argument tab (Logical_Test) and then click on 45,000 which is the monthly sales of our 1st employee.

Notice, cell address C7 pops up in the tab. Type >= to check a condition of Greater than or Equal to and then click on 34,000 which is a target given to all agents.

Cell address of target D2 pops in the tab. As, target remains fixed for all the sales agent and we don’t want this cell to move down relative to our formula, we will lock this cell by plugging in the dollar signs $D$2. (F4 key can also be used as a shortcut). Also, notice TRUE appearing on the right side of our tab which indicates that condition stands TRUE for our 1st sales agent.

 

excel exert

 

2. Click in the second argument tab (Value_if_True). Here we wish to calculate commission which is 5% of his monthly sales. So, we will first click on 45,000 (cell address: C7), type asterisk (*) which works as a multiplication operator in Excel and then click on 5% (cell address: D3). Like target, commission also remains fixed so we will also lock this cell $D$3.

 

excel exert

 

3. Click in the third argument tab (Value_if_False). Here we will type ‘0’ as the employee did not meet the required Monthly sales target.

excel exert

4. Click OK and you will see Commission of 1st employee appears in the cell D7.

excel exert

5. Point towards the bottom right corner of the cell until you see four headed black arrow (also called fill handle). Then double click to populate the remaining cells with the IF function.

excel exert

 

There you have it! One of the most useful and effective Excel functions that is sure to save you a lot of time and hassle. Check out an Excel Course in a city near you.

What is Remote Training?

Valued by Individuals

 

4.8/5
Over 21470 Reviews
Jagg was an excellent trainer - very patient, had lots of options for people who didn't have the newest versions. I feel really confident about what I learnt today and I am very happy I did this course! -Power BI Beginner Adelaide
Melissa - Power BI Beginner, .
Excellent! Very knowledgeable instructor. -Power BI Beginner Adelaide
Jason - Power BI Beginner, .
Great course, very informative. Great instructor, very helpful -Power BI Beginner Adelaide
Sarah - Power BI Beginner, .
Great session, moves at a good pace with support when needed. Thanks -Power BI Beginner Adelaide
Matthew - Power BI Beginner, .
Excellent! -Microsoft Applications - Customised Sydney
Candice - Microsoft Applications - Customised, .
Very helpful -Microsoft Applications - Customised Sydney
Janet - Microsoft Applications - Customised, .
The learning environment and facilities are great. Ken knew the course content and is a friendly trainer.. -SharePoint Site Owner Melbourne
Robbie - SharePoint Site Owner, .
Thank you Ken Great 2 day of learning lots of information, looking forward to making our New Company SharePoint Site -SharePoint Site Owner Melbourne
Natasha - SharePoint Site Owner, .
Thank you Ken, I feel a lot more empowered and up-skilled with regards to SharePoint. I am looking forward to supporting my organisation to leverage SharePoint as one of collaboration and information management tools. -SharePoint Site Owner Melbourne
Annita - SharePoint Site Owner, .
Found it beneficial. Ken was extremely knowledgeable -SharePoint Site Owner Melbourne
Toby - SharePoint Site Owner, .
Great course, learnt a lot for the trainer. -Power BI Beginner Sydney
Aaron - Power BI Beginner, .
A good session however some revision of content from the intermediate course, which meant doing those two courses close together made those portions somewhat repetitive. Jagg was a good presenter and a good pace. -Excel Advanced Adelaide
Belinda - Excel Advanced, .
Jagg went above and beyond to help us learn the advance functions of excel. -Excel Advanced Adelaide
Ateeq-ur - Excel Advanced, .
Jagg was a great instructor - he stepped us through at a good pace, paused well to take q's and was well prepared. -Excel Advanced Adelaide
Hannah - Excel Advanced, .
Excellent range of advanced skills presented -Excel Advanced Adelaide
Oliver - Excel Advanced, .
Jag was excellent! Clear and thorough. I was a bit worried doing it remotely but found it better! Highly recommend. -Excel Advanced Adelaide
Agland - Excel Advanced, .
Was a very good informative session from Jag. -Excel Advanced Adelaide
Nilmini - Excel Advanced, .
Thank you. Well taught, clearly presented, and valuable lessons. Would recommend. -Excel Advanced Adelaide
David - Excel Advanced, .
Good pace, great course -Excel Intermediate Melbourne
Gemma - Excel Intermediate, .
Was a very informative course. John is a very patient instructor. Gives clear instructions. -Excel Intermediate Melbourne
Sonia - Excel Intermediate, .

Trusted by Business

Procured by Government

Map of Australia