The Conditional Formula

A number of specialised functions are available on Excel, one of these is the Conditional Formula. Consider the effect of overtime on our example:

Overtime is calculated by subtracting the normal weekly hours from the actual hours worked. Consider Jones, if the normal weekly hours for each employee is 38 hours, then Jones has worked 56 hours - 38 hours = 18 hours overtime.

This calculation appears straightforward until we consider Lewis, Lincoln and Smythe, the three employees who have worked less than the 38 normal hours. A simple subtraction would mean that they have worked minus overtime hours. The use of the conditional formula will allow us to subtract for employees who have worked more than 38 hours, but to give the answer 0, for employees who work 38 hours or less.

The Conditional Formula consists of three parts:

i. The Rule Or Condition.
ii. The Outcome if the Condition is True.
iii. The Outcome if the Condition is False.

=If(Condition, Outcome if True, Outcome if False)

In the above example, employees work more than 38 hours (Condition), overtime will equal hours worked minus 38 hours (Outcome if True), otherwise, overtime will equal zero (Outcome if False).

  1. Insert two new rows at row 5.
  2. Insert a new column at D.
  3. Enter the label Normal Hours in cell B5, and 38 in cell E5.
  4. Enter the label Overtime Rate in cell B6, and 150% in cell E6.
  5. Insert a new column heading Overtime Hours in cell D8.
  6. Enter the conditional formula in cell in D9:
    1. =If (C9>E5, Condition
    2. C9-E5, Outcome if True
    3. 0) Outcome if False
  7. As a formula, this appears as =If(c9>E5, C9-E5, 0). NB. Note the use of commas, to separate Conditions and Outcomes.

Copying this formula down to cell D13, your spreadsheet should now appear as:

Insert two new columns at F. Enter the title Normal Pay in cell F6 and Overtime Pay at cell G6. Calculate normal pay by multiplying Hours (C9) by Hourly rate (E9). Calculate overtime pay by multiplying Overtime Hours (D9) by Hourly Rate (E9) by Overtime Rate (E6).

Complete this exercise making any necessary changes to the formula in columns H,J,K and L.

The conditional formula may also be used to display a message as an outcome. Consider the following example:

  1. In the Popularity column, display a message to state which programme is the most popular, depending upon the answer in the Viewing Figures column. To display a message quotation marks ("") must be used.
  2. In cell C3, enter this formula: =If(B3>B4,"Coronation Street is more popular than Eastenders","Eastenders is more popular than Coronation Street")
  3. Replicate this formula in cell C4 to display the appropriate message. Amend the figures to read Coronation Street 54.3 million, Eastenders 56.7 million. The messages should now change.

Contents: Spreadsheets

Skill Check: Spreadsheets