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:

Excel If Function with calculations 1
  • 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:

Excel If Function with calculations 2

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)

Excel If Function with calculations 3

=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:

Excel If Function with calculations 4

=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:

Excel If Function with calculations 5
Next TopicExcel COUNTIF




Latest Courses