Excel is a tool with which we can perform a plethora of calculations, from the simplest to the most complicated.
In this tutorial, we focus on the IF function of Excel. Each formula is illustrated by an example of the situation, but of course it adapts to the needs of each.
The original article is by JD Sartain : Excel logical formulas: 12 simple IF statements to get started, posted on PCWorld.com.
1. What is the IF function ? .
The IF function is used to determine if a condition is met or not, and if it is not then another condition, created by the user, is provided.
In summary, it enables you to perform logical comparisons between a value and the expected result. This function may have two results because if the first result is proved then it must be applied. If it is not then it is the 2nd that will be.
The logical formula is If a is true, then apply this solution, otherwise apply another solution. For example, if it rains, then close the windows, otherwise leave them open.
The syntax of the Excel or the structure of this formula IF is like this : =IF(test_logique,valeur_si_vraie, valeur_si_fausse).
2. Notifications of delay .
In our first worksheet, the accountant of a company must verify if each payment has been received by the due date or not.
The due date is specified in the column A, the status of payment is shown in column B and the client name is in column C.
1 – In column A, cell A1, enter the formula : =TODAY(), to display the current date.
2 – In column B, cell B4, enter the formula : IF(A4-TODAY()>30;”Overdue”;”OK”).
It will be used to determine which customers are in arrears of over 30 days.
In other words, the formula means : If the date in cell A4 is less than the date today is greater than 30 days, then enter “late” in cell B4, otherwise, enter “OK”.
3 – It only remains for you to do is drag your cursor to cell B4 to the cell B13 to apply the formula across.
3. Success/Failure in in-house training .
To supplement internal training, employees must pass a test.
The candidates, totalling a score of less than 70 % fail the test, if it is higher then they pass.
1 – In column A, list the names of all the candidates.
2 – In column B, see their result.
3 – In column C, it will display the success or failure of the examination. This information is obtained by the IF function.
4 – In cell C4, enter the formula : =IF(B4<70;”Fail”;”Success”).
This means that if the result in cell B4 is less than 70 then enter “Failure”, otherwise enter “Success”.
It only remains for you to do is drag your cursor up to the cell C13.
4. The nested IF functions .
In this example, we will convert the notes to review encrypted letters through formulas nested IF. This is to join multiple functions in a single formula.
The name of the student is entered in column A, their numerical scores in the B and their equivalent in letters, calculated thanks to the functions SO, will be in column D.
We have entered in cell C7, the formula is as follows :
You then simply drag your mouse to the last cell of the table.
Note that each open parenthesis must be closed at the end of the function.
5. Commissions on sales and bonuses .
Companies Wilcox pays 10 % commission to their marketing team if it manages to achieve more than 10 000 euros of sales. If the amount is less than then it does not collect a bonus.
1 – Enter in column A the name of each member of the marketing team.
2 – In column B, enter the total amount of sales in the month.
3 – In column C, multiply the total sales by 10 %, that is to say, =SUM(B4*10%). Drag the cursor from the C4 to C13.
4 – Finally, in column D enter the formula =IF(B4>10000;C4;”No Bonus”).
It will then be displayed the amount of commission income in €, if it is higher than 10 000 €, or “No Bonus”.
Then, slide your cursor to D4 to D13.
5 – now, to calculate the total bonus, enter in column D15 =SUM(D4:D13) and press the Enter key.
6. Commissions as a percentage, and nested functions .
In this example, we will use another technique IF function nested. This allows you to calculate several percentages of commissions, and then carry the totals of these monthly commissions.
1 – In column A is the name of the members of the marketing team.
2 – In column B is carried forward each total amount of sales made over the month.
3 – In column C are entries to the commissions.
4 – In cell C4, enter the formula: =IF(B4<5000;B4*7%;IF(B4<8000;B4*10%;IF(B4<10000;B4*Of 12.5%, B4*15%)))
5 – Finally, in cell C15, will be registered the total amount calculated automatically with the IF function.
Enter in C15, use the formula : =SUM(C4:C13).
7. The price of a product calculated on the basis of the quantity .
A lot of dealers, especially the wholesalers give their clients a quote based on the quantity of products.
For example, a wholesaler can sell 1 to 10 T-shirts to 10.50 €, and 11 to 25 T-shirts at 8.75 €.
Use the following formula to determine the savings you’ll realize by purchasing products wholesale :
1 – Name the cell A3 wholesale Price, the cell B3 Amount and cell C3 Cost.
2 – B4 to B9, enter the different quantities and their respective prize in column C.
3 – now, create a second table with the products to sell (A12 to A18), the quantity ordered (B12 to B18).
4 – Finally, enter a name for cell C11 Cost in the amount and in the cell below it, enter the formula :
5 – finally, enter in the cell for the total : =SUM(C13:C19).
If your prices and quantities change, then you will enter the data, Excel will adjust the formulas and calculations.
8. Use other formula in the IF function .
In this example, we will nest the 2 functions IF, and insert the formula AMOUNT. The purpose is to determine if the goals of a person are less than, equivalent to, or superior to those of the company.
1 – Enter in cell B1 theMonthly Target. It can be changed each month to reflect the expectations of the company.
2 – Name the cells A3 to G3 as follows : Goals, 1st Qtr, 2nd Qtr, 3rd Qtr, 4th Qtr, Goals and Bonus.
3 – From cell A4 to A8, enter the objectives of the 5 best members of the marketing team.
4 – Finally, enter the total quarterly every commercial, from cell B4 up to the E8.
Those who have achieved their sales targets will earn 10 % bonus of the total amount in euros.
For those who have exceeded, the bonus will be 25 %.
Here’s how to calculate, respectively, the objectives and the bonus in € :
5 – Enter in the cell F4 =IF(A4B1,SUM(B4:E4,0))) and drag it up to cell F8.
6 – Then, in cell G4, enter : =IF(F4=B1,F4*25%,0)) and glissez‑up to G8.
9. Use the IF function to avoid divide by zero .
In Excel, when you try to divide a number by zero, it appears the error message : #DIV/0!.
So how to do if you have a large database that requires divisions, and that some of the cells contain the number zero ? The solution is the IF function.
1 – To calculate the money collected each month by the volunteers of the charitable organization, (that we invented) Hope and Life, name 4 columns in your table as well : A1 € , B1 No. (number) of volunteers, C1 Euro by the volunteers and D1 Euros remaining.
The amount entered in column A will be divided between each volunteer.
The number of volunteer may be 0 because some days the organisation is closed.
2 – In cell C2, up to the C11, enter : =IF(B20.A2/B2.0). You will see that with the IF function, it is possible to divide your amounts by zero.
3 – In cell D2, copy the formula =IF(B4=0;A4;0) and drag it up to D11. If there is no volunteer, then no amount of money will be paid.
4 – Now go to B2 and enter =MAX(B2:B11), it is used to identify the maximum number of volunteers, which is 8.
5 – finally, in cell D12 and type =SUM(D2:D11)/B12.
In total, 8 volunteers will share 392,50 €.
10. To go any further .
If you want to use Excel and other Office tools on a Mac, don’t hesitate to check out our tutorial.
If you want to improve your skills in desktop, there are online training courses via the’free application Excel and PC 7,89 €.
From LinkedIn,you can train free for a month to Excel, Outlook, Word, PowerPoint.
Please be aware that there are also Microsoft Certifications Office.