91-9990449935 0120-4256464 |
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: =if(B2>5,B2*2,B2*4) 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) =IF(B2<=50,B2*120%,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: =IF(B2=60,"",C4*D3+5) 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 Advanced If Functions
|