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.

Contact Us

What is Remote Training?

Valued by Individuals

4.8/5
Over 25024 Reviews
Handy Course! Teacher had great knowledge of the software. -Excel Advanced Perth
Nick - Excel Advanced, .
Awesome! -Excel Advanced Perth
Eva - Excel Advanced, .
I very much enjoyed the course and found Steven to be very helpful. I was very fortunate to be the only student in attendance. I had some knowledge but feel I will leave today with far more knowledge. -Excel Intermediate Brisbane
Shirley - Excel Intermediate, .
Jagg was wonderful. His approach to teaching was very suitable to the needs of the class. He worked at a pace that was easy enough to follow and allowed for questions when necessary. Keep up the good work! -Project Beginner Adelaide
Billie - Project Beginner, .
The course was designed well and eased into some of the more difficult components. Great trainer and able to help articulate learnings into what makes sense to specific projects. -Project Beginner Adelaide
Amanda - Project Beginner, .
A fantastic course. Very helpful and informative -Project Beginner Adelaide
Tanya - Project Beginner, .
good facilitator thx for today -Project Beginner Adelaide
jeff - Project Beginner, .
Excellent beginners course - feel confident to get started! -Project Beginner Adelaide
Kim - Project Beginner, .
Ken was absolutely fantastic! -Excel Beginner Melbourne
Amrita - Excel Beginner, .
Very helpful base level of knowledge across the platform. Looking forward to the intermediate. Super friendly trainer, thanks Domenic. -Office365 Beginner Perth
Lei - Office365 Beginner, .
Very interactive -Office365 Beginner Perth
Mark - Office365 Beginner, .
Great Excel course, will be very useful in improving my work efficiency. Hope to complete the Advanced Excel Course in the future. Thanks Jenny. -Excel Intermediate Perth
Thomas - Excel Intermediate, .
Very very good course. Jenny is amazing and a great teacher!! A great pace / environment to learn in and ask questions. -Excel Intermediate Perth
Morgan - Excel Intermediate, .
Great course - it's going to save me a lot of time at work and make life easier. Jenny was very helpful and clear at explaining the concepts. -Excel Intermediate Perth
Blaine - Excel Intermediate, .
Jenny was incredibly helpful and friendly. She answered whatever questions we had. The functions taught within this class would be very useful for my work within HR as well as projects. -Excel Intermediate Perth
Alison - Excel Intermediate, .
Learned lots, was very easy to follow. -Excel Intermediate Perth
Oliver - Excel Intermediate, .
It was great! -Excel Intermediate Perth
Alessandra - Excel Intermediate, .
Very useful information to convert into both a working and personal environment. -Excel Intermediate Perth
Mia - Excel Intermediate, .
I enjoyed the course. Jenny was very helpful and informative. -Excel Intermediate Perth
Celeste - Excel Intermediate, .
Great, learned even more functions of Excel -Excel Advanced Brisbane
Leonie - Excel Advanced, .

Trusted by Business

Procured by Government

Awards and Accreditations

Map of Australia

Follow us