About Microsoft Excel

IF and Nested IF Function in Microsoft Excel

Home Back Next





The IF function in Microsoft Excel and the related nested if function are very important functions indeed. In fact you can say the complete functioning of the computer is based on IF.
The syntax of the IF function is:
=IF(logical test, value if true, value if false)
So for example if an employee makes a sale of 100000 or more in a quarter he gets an extra 10% commission over and above his basic salary. If he doesn't, he gets no extra commission. Let's say an employee gets a basic salary of 12000. Now any employee who makes sales of 100000 or more gets (12000+10%*12000)=13200 and the employee who doesn't achieve that goal gets only 12000. So the above formula would now look like this:
=IF(Sales>=100000,(12000+10%*12000),12000). Of course, you can replace the word 'sales' with a worksheet cell address like 'A2'.
We read the above formula as: 'If the employee makes sales of 100000 or more he gets 13200 else he gets 12000'.
Similarly we could write '=If(marks>=90,"A","")' to assign a grade A if the vaue of the marks is greater than 90 or leave the grade area blank if the marks are not greater than 90.
What do we do if the student doesn't get 90 or more marks and we still wish to assign some grade? We use the nested if as shown in the training video:
=IF(F4>=80,"A",IF(F4>=70, "B", IF(F4>=60,"C",IF(F4>=50,"D","Work Harder"))))
The above formula is read as:
If the students marks shown in the worksheet cell address is greater than or equal to 90, then he is assigned a grade A, elseif his marks are greater than or equal to 70, then he is assigned a grade B, elseif his marks are greater than or equal to 60 then he is assigned a grade C, elseif his marks are greater than or equal to 60 then he is assigned a grade C, elseif his marks are greater than or equal to 50 then he is assigned a grade D else he is assigned "Work Harder". Since the grade assignments are text values we need to enclose them in quotes. We start from the highest number of marks and check step by step which condition is true and assign the relevant grade. In case none of the marks falls in the criteria ranges we assign the text value "Work Harder". Please note that we can also start from the lowest number of marks and the assignments of grades has to be changed accordingly.
Now you know how to use the if and nested if functions in Excel and why they are so important.
Note: To use multiple conditions in an if function you can also use 'AND' and "OR' which we'll discuss in another training video.

Home Back Next