Scribd
Upload
15 views

Exercise 5 - If Functions

task

Uploaded by

ohriaahaan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
15 views

Exercise 5 - If Functions

task

Uploaded by

ohriaahaan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Exercise 5 – If functions

The IF function can be used to decide what text to display (‘Correct’ or ‘Incorrect’). We’ll start with a simple solution
and then look for ways to improve on it. You may use Excel or Google Sheets for this task.

a. Create a spreadsheet like the one


shown on the right.

Note: If you try and type the equals


sign in Cell E8, the spreadsheet will
think you are writing a formula. It is
necessary start with a single quote
mark i.e. '=

b. Name the worksheet ‘Multiply’ and save. For the moment, the spreadsheet doesn’t do anything.

– The IF Function
The IF function allows us to display different values or text in a cell depending on some condition. In our case, we
want to ask a question then display the comment ‘Correct’ if the answer entered is correct, or ‘Incorrect’ if it is not.
The IF function works as follows:

IF the answer is correct


THEN display the word “Correct”
ELSE display the word “Incorrect”

In your spreadsheet, the formula will look like


the one on the right.

What does the formula mean?

= IF ( F8 = B8 * D8 , "Correct" , "Incorrect" )

All formulas IF … … the value in F8 … THEN … … display … ELSE … … display


start with an equals B8 the word the word
equals sign multiplied by D8 … ‘Correct’ … ‘Incorrect’

a. Select cell F8. This is where the text


‘Correct’ or ‘Incorrect’ will be displayed.
Type the formula as shown. Be careful – a
missing bracket or quote mark will stop
the formula working.
b. Test your spreadsheet by entering different values into cells B8, D8 and F8.

Inserting Functions in Excel


In Excel, you can use the menus rather than typing out
formulas. To test this, select a spare cell and click
‘Formulas / Logical / IF’. Complete the boxes as shown and
click OK. Alternatively, click on the Insert Function icon to
open or reopen this window.

Extension Tasks (of Increasing Difficulty)

1. Display Instructions?
At the moment, our three instructions are always visible. We can decide whether to display each instruction
depending on whether each box has something in it or not. Use a pair of double quotes to mean empty.

The formula on the right therefore says:

If cell B8 (i.e. Box 1) is empty, display the


text shown, else leave this cell empty.

Copy your worksheet and name the copy


‘Extension’. The entire worksheet can be selected
by clicking in the rectangle in the top-left, then
copying and pasting.

Type the formula into cell B3, and similar


formulas into B4 and B5 referring to Box 2 and
the Answer cell.

Check that only the instructions that haven’t


been completed are visible.

2. ELSE IF
You may have used the term ‘ELSE IF’ when considering algorithms before. The problem with our spreadsheet is
that it displays the word ‘Correct’ or ‘Incorrect’ even when the answer box is blank. It would be better to have
no words displayed until there is an answer in place.

IF the answer box is blank


THEN display nothing
ELSE IF the answer is correct
THEN display the word “Correct”
ELSE display the word “Incorrect”

There isn’t an ‘ELSE IF’


statement in a spreadsheet,
but you can place one IF
statement inside another.

What does the formula mean?

= IF ( F8 = "" , "" , IF ( F8 = B8 * D8 , "Correct" , "Incorrect" ) )

IF … … the value … THEN … … leave … ELSE … … IF etc … Remember to close


in F8 is this cell all your brackets.
blank … blank …

Try out this formula in cell G8. No word should be shown unless you enter an answer.

3. OR (a Conditional Operator)
This is getting tricky now, but if you’re a real expert, you
can add a number of conditions inside a bracket with a
conditional operator. Use this method to only display a
word if all three boxes have been filled. Separate your
conditions with commas.

4. Why Stop There?


Still going strong? Complete the work on our original
algorithm by putting all the instructions in one cell and only
showing the one that’s required. A formula like the one
below should do it (Note: We’ve deleted a couple of rows
from our spreadsheet that are no longer required).
Remember to close all your brackets at the end.

You might also like