7.7 Logical functions

Logical functions depend on the values true and false. The value returned by a logical function can be a number, formula, action or reference, or the values true or false. However, the result depends on the initial logical test that results in true or false.

The IF function

The IF function is powerful in that it provides a choice of entries to a cell conditional on the values of other cells in the spreadsheet. We start with a test, then a course of action if it is true or another if it is false; for example, ‘If the temperature will be over 20, then don’t take a coat out, otherwise take a coat’.

So the condition, or test, is ‘is temperature over 20?’ If the result is yes or true, the message ‘don’t take a coat’ will appear. If the answer is no, the result is false, so the message ‘take a coat’ will appear.

In Excel this would be written as:

=IF(temperature > 20, ‘don’t take a coat’, ‘take a coat’)

In general:

=IF(condition, value if true, value if false)

The condition in the IF statement often uses the following operators:

= equal to

≥ greater than or equal to

≤ less than or equal to

> greater than

< less than

Before we use this function, try determining the three components of the IF function for the following situation:

You have decided that if total profit from the concert you are organising is over $10,000 then you will throw a party for the helpers, otherwise a memo will simply be sent to all helpers thanking them for their efforts.

= IF (_______________________,_______________________,_______________________)

Spreadsheets exercise 9

The IF function 1

Skills practised
  • New file
  • Entering text
  • Entering functions

In this sheet you are using the IF function to determine which bills have been paid. If the bill is paid then ‘Nil’ appears in the cell. If the bill is not paid then the value of the bill is entered into the cell.

  1. Open the workbook Paid downloaded from the above link. The IF function is used to test the contents of the Paid column. If a Y appears to indicate the invoice is paid then the text Nil is stored in the cell. However, if a Y does not appear then the contents of the Total column are stored in F2 to be included in the sum amount owed.
  2. On paper, work out how the IF function should be entered.
  3. Click on cell F2 and use the IF formula to display the appropriate message in the Balance column. For example =IF(E2=“Y”, “Nil”,C2).
  4. Repeat for the next few rows for practice, then copy down the column.
  5. Now to test your formula. Click in cell E7 and type Y - Nil should appear in the Balance column.
  6. Save and close the file.
    P0737

Spreadsheets exercise 10

The IF function 2

Skills practised
  • New file
  • Entering text
  • Entering functions
  1. Open the file Internet charge. The first two hours of any session are free. In column L you will use an IF function to check the length of connection.
  2. Click on K5 and type =Hour(J5) – this will convert the hour value in J5 into a numerical value that can be used in calculations.
  3. Copy the formula down the column. The connection fee is free if less than 2 hours are used. If more than 2 hours are used then there is a cost.
    P0738.jpg
  4. Click on L5 and enter the formula =IF(K5<2,”FREE”,”COST”).
  5. Click on L6 and repeat.
  6. Copy the formula down the remainder of the column.
    P0739

Spreadsheets exercise 11

The IF function 3

Skills practised
  • Editing formulas
  • Copying formulas
  1. In the Internet charge file click on cell L5 and edit the formula so it reads: =IF(K5<$D$2,“Free”, K5*$H$2)
  2. Click on cell L6 and edit the formula appropriately (just for practice).
  3. Copy the formula down the rest of the column.
    P0742

A formula can be the result of an IF statement. In the next exercise, instead of displaying cost, a formula is used to display the fee for connection, which is $1.20 for each hour after the first two hours.

The fee for connection per hour is stored in cell H2. The limit of hours for free connection is stored in cell D2. References should be made to these cells within the IF function rather than entering values directly into the formula.

Nesting IF functions

IF functions quite often involve nesting functions. A nested function is where one or more functions are used within a function.

In the next exercise you will adjust the formula that calculates the fee payable for internet connection. The connection fees have been altered so that connection for longer then 7 hours is still charged at $8. So if connection is less than 2 hours it is still free. If it is greater than 2 hours and is also greater than 7 hours then the fee is $8 (stored in cell K2). However, if it is less than 7 hours the fee is still $1.20 per hour.

Spreadsheets exercise 12

Nesting IF functions

Skills practised
  • New file
  • Entering text
  • Entering functions
  1. In the Internet charge file click on cell L5.
  2. In the formula bar, delete the false section K5*$H$2 and enter the nested function IF(K5*$H$2>$K$2, $K$2, K5*$H$2).
  3. Repeat for the next few cells for practice then copy down the remainder of the column.
    P0742
×
0002.jpg