IF examples 1

Please purchase the course before starting the lesson.

IF this then that Examples

IF Ex 4

In this example we want to be able to switch between 2 different scenarios for the sales and selling price. In B16 and B17 rows use IF statements to tell Excel which cells to use depending on what the numbers are in B1 and B2

  • In B16 row use an IF statement to tell Excel that if cell B1 is a 1, then use B6 otherwise use B7. Remember to think about the $ signs so that you can copy it across.
  • In B17 row  use an IF statement to tell Excel that if cell B2 is a 1, then use B11 otherwise use B12. Remember to think about the $ signs so that you can copy it across.

IF Ex 5

In the yellow cells, use an IF function so that they calculate the commission payable based on the blue cells ( a 5% commission once the sales are over R20 000.

IF Ex 6

We have a listing of employees and how many hours that worked on a number of days. We need to allocate the hours to normal time (8 hrs), overtime (anything over 8 hrs on a day) and leave used (anything less than 8 hrs)

  • In E11 column, use an IF formula to either show 8 hours or the lesser number if less than 8 hours are worked i.e. if the hours worked was 12 then this column must show 8, if the hours worked was 6 then it must only show 6.
  • In F11 column show how many hours overtime were worked. If the hours shown in column D are more than 8 then show the difference between the hours worked and 8 otherwise show 0 i.e. if the hours worked was 12 then it must show 4 (12 minus 8) otherwise it must show 0
  • In G11 column show how many hours must be deducted from leave where the employee worked less than the minimum 8 hours. So if the hours worked was only 6, this column must show 2 (8 minus 2) but if the hours worked was 12 then it must show 0, as they have worked there minimum amount.

To see the full course contents click here.

Back to: Advanced Excel Course > Function and Formula Basics