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:
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:
Next TopicExcel COUNTIF