Excel If Function with Calculations
“IF function” can be used in complex calculations. See the example:
If a sales executive sells more than 5 items, the company will pay incentive 40 rupees per item sold and if the sales executive sells less than 5 items, the company will pay 20 rupees per item sold.
See how the “IF function” is used with the calculation:
- Select the cell in which you want to create the “IF function”
- Type the code in the cell: =if(
- Type the condition with comma: C4>5,
- Type what you want to show if condition is fulfilled: 40*C4
- Type a comma: ,
- Type what you want to show if the condition is not fulfilled: 20*C4
- Then close the bracket and press the Enter key.
The IF function will look like this: =if(C4>5,40*C4,20*C4)
So the IF function says if value in cell C4 is greater than 5 then multiply it with 40. If it is less than 5 then multiply it with 20.
The IF function can be modified to perform different calculations:
Suppose in the above example the company wants to pay rupees 50 along with incentive to those employees who have completed probation period of 5 months or their job duration is more than 5 months.
In this case, we can insert one more column in the worksheet for job months and modify the IF function accordingly to get the results.
The modified IF function is: =if(C4>5,50+E4,E4)
It says if the value in cell C4 is greater than 5 then add 50 to E4 which is incentive of Peter. And if it is less than 5 then keep the incentive, the value of cell E4 same. See the image shown below:
Few more examples of IF Function:
The IF function says if value in Cell B2 is greater than 5 then multiply this value with 2 else multiply this value with 4. See the image given below:
The IF function can also be composed in this way: =IF(B2>5,2*B2,4*B2)
The IF function says if the value of cell B2 is less than 50 or equal to 50 then increase it by 20% else display the same value without any change. See the image given below:
The IF function says if the value of cell B2 equals to 60 then leave the cell blank else multiply the cell C4 with D3 and add 5 to it. See the image given below: