147

8 Spreadsheets

In this chapter you will learn how to:

★ create and edit the structure of a spreadsheet

★ manipulate cells and their contents

★ freeze panes and windows

★ create formulae and use functions

★ use validation rules

★ formatcellswithinaspreadsheet

★ createatestplanandtestthespreadsheet

★ extractsubsetsofdatawithinaspreadsheet

★ sortthedatawithinthespreadsheet

★ createpivottablesandpivotchartsto

summarise data

★ import and export data

★ create macros to automate operations within

aspreadsheet

★ createagraphorchartappropriatetoa

specific purpose

★ applyformattingtoagraphorchart.

Before starting this chapter you should:

★ have studied validation and verification

(Section 1.4)

★ understandtheterms‘cell’,‘row’,‘column’,

‘sheet’, ‘tab’, ‘page’, ‘chart’

★ be able to enter text, numeric data, formulae

andsimplefunctionsintoaspreadsheet

★ beabletouseeditingfunctionssuchascut,

copy, paste and format painter

★ beabletochangethesizeofrowsand

columns within a spreadsheet

★ beabletoreplicateformulaeandfunctionsin

the spreadsheet

★ understand the order of mathematical

operations in formulae

★ understand the types of mathematical

averages: mean, median and mode

★ beabletosaveaspreadsheet

★ beabletoprintaspreadsheetdisplaying

formulae or values

★ understand the terms ‘header’and‘footer’

and why they are needed.

For this chapter you will need these source files:

Q AlpHotels.csv

Q Ashford.csv

Q Athletics.csv

Q Bolton.csv

Q Bolton2.csv

Q BuildingProjects.csv

Q BusAnalysis.csv

Q BusTimes.csv

Q Cardiff.csv

Q CityTemp.csv

Q Class.csv

Q Client.csv

Q Client1.csv

Q Dundee.csv

Q Employees.csv

Q Employees2.csv

Q Employees3.csv

Q Flights.csv

Q Flights1.csv

Q Format.csv

Q Gradebook.csv

Q Hotels.csv

Q JanSales.csv

Q January.csv

Q Motherboard.csv

Q Operators.csv

Q Orders.csv

Q Quarter.csv

Q Rain.csv

Q Rates.csv

Q Roles.csv

Q RoomRates.csv

Q Rooms.csv

Q Rounding.csv

Q Salary.csv

Q SkiRace.csv

Q Socket.csv

Q Staff.csv

Q Stock.csv

Q Strings.csv

Q Tasks.csv

Q TBCWorkers.csv

Q Test.csv

Q TGCosts.csv

Q TMC.csv

Q TMCColour.csv

Q TMCVehicles.csv

Q TPAS.csv

Q Traffic.csv

Q TWTWorkers.csv

Q WorkLocation.csv

148

8 SPREADSHEETS

8.1Createaspreadsheet

The spreadsheet structure will often be given to you by the organisation that

you are creating it for. This may be given in diagram form, or as a specification

sheet, or for larger organisations as two specification sheets, one for the design/

layout and one for the organisation’s house style. These specifications will

often consist of font styles (including typeface and font sizes), colour schemes/

themes, page styles (including page size and orientation), as well as other

requirements like the use of gridlines and the visibility of row and column

headings.

You will need to take these specific requirements into account as well as the

intended user (audience) of the spreadsheet. Where specifications are not given,

consider the font face, size and colour themes that would be appropriate for

the user and pay attention to keeping the text easy to read, which can include

considering the reading age and technical expertise of the intended audience.

Spreadsheet basics

A spreadsheet is a two-dimensional table split into rows and

columns. It has a structure containing individual cells like this.

Each cell has an address. In this example, the cell with the

cursor in it is called cell E9 and the cell that has been coloured

red is called cell C6. The red cell and all of the yellow cells are in

column C, and the red cell and all of the blue cells are in row6.

A spreadsheet is sometimes called a sheet or even a worksheet.

In Excel, many sheets/worksheets can be held within a single

workbook.

Each cell in a spreadsheet can hold one of three things. It can

contain:

» a number

text, including hyperlinks, which is called a label

» a formula, which always starts with an = sign.

As a reminder, for this and all following chapters, the text following the Tasks

demonstrates the techniques used to solve the Task and gives some example

answers. These provide easy-to-follow step-by-step instructions, so that practical

skills are developed alongside the knowledge and understanding.

Task 8a

Open the file Staff.csv inanew

workbook. Place the file Rates.csv

withintheworkbookasanew

sheet called Rates.Addanew

sheet called Location within the

workbook by typing in the data

so that it looks like this. Save the

spreadsheet as Task_8a.

149

8.1 Create a spreadsheet

Importing data into Excel

Comma separated values (.csv) is a file format often used for source files.

This file type can be imported into almost all spreadsheet packages on any

platform, although the delimiters (usually commas) may be different for your

regional settings. To attempt Task 8a, select the

File tab, then Open. Locate

the correct folder, but note that the csv file will not be visible. Click on the

drop-down menu for the file type to get a list of available files.

Select Text Files to see any .csv or .txt files. Double-click on the file Staff.csv

to open it.

We need to import the file Rates.csv into a new worksheet within the current

workbook.

Importing a .csv file into Excel

Data can be imported into a sheet using the

Data tab.

In the

Get & Transform Data group, select From Text/CSV

This opens the Import Data window.

Double-click on the file Rates.csv to open the text import window.

Check that the delimiter is set to

Comma before clicking on the

button.

Advice

Ifyouuseregionalsettingswithsourcefilesthatdonotusecommasasdelimiters,

select the appropriate option for the regional settings of your source files.

150

8 SPREADSHEETS

The data has been imported into a new worksheet which is given

a name by Excel, in this case ‘Sheet1’.

The data may have been formatted by Excel with alternate green

and white row shading. It can be reformatted from the Table

Design

tab, in the Table Styles section, by clicking the up arrow

twice and selecting the plain white style which is on the left.

Uncheck the tick box for the

Filter Button to remove this.

To rename this sheet, move the mouse over the sheet name ‘Sheet1’ and press

the right mouse button to open the drop-down menu. Select Rename from this

menu and the text ‘Sheet 1’ will be highlighted. Overtype the highlighted text

with Rates and press

<Enter> to make this change.

You now need to create a new worksheet called Location. To create a new

worksheet, click on the New Sheet button.

This adds a new worksheet which can be renamed, and the new data entered

in to it. Save your work with the filename Task_8a as a spreadsheet (not in .csv

format).

Activity 8a

Open the file TMC.csv inanew

workbook. Place the file

TMCColour.csv within the

workbookasanewsheetcalled

Colour.Addanewsheetwithinthe

workbook called Sites.Typedata

into this sheet so that it looks like

this. Save the spreadsheet as

Activity_8a.

8.1.1 Create the structure

Page specifications

The page orientation, page size and margins all relate to printed versions of

the spreadsheet. Although printed pages are not always required, you may be

required to export your spreadsheet as a document (often as a

.pdf file), so

formatting your sheet for printing is still an important skill to learn.

To change the formatting, select the Page Layout tab. The Page Setup window

(we will look at this later) can be opened using this arrow.

This is useful if you

need to change more

than one specification

at once.

Page size is accessed

using the drop-down

arrow like this.

151

8.1 Create a spreadsheet

The drop-down menu offers a list of document sizes (pre-set by

the software using your regional settings). Select the required

page size from the list.

Advice

Ifyourequireapagesizethatisnotshown,selectMore Paper

Sizes... (at the bottom of the list) to open the Page Setup window.

Use the drop-down Paper size list to select the required size.

To change the page orientation, use the drop-down arrow to

display the options.

Select either Portrait or Landscape from the list.

Advice

Wherespecificationsdonotshowportraitorlandscape,selectthe

most appropriate for the design of the printout. Make sure that all

data, labels (and if appropriate formulae) are fully visible and in a

font large enough to be read easily.

Page margins

Page margins contain the whitespace between the edge of the spreadsheet

and the edge of the page. These can be changed to give documents a more

professional look, for example placing data in the middle of a page rather than

squashed into the top-left corner. They can also be changed to make more data

fit on to a single page, without reducing its size and readability. Page margins

can also be changed by selecting the

Page Layout tab and using the drop-down

arrow to display the options like this.

To open the

Page Setup window in the margins tab, select Custom Margins….

152

8 SPREADSHEETS

Remember this can also be selected directly

from the Page Layout tab.

You can adjust the margin settings for

Top,

Bottom, Left and Right, then select the

Print Preview button to see the effect of the

changes.

Advice

If you are printing a document, make sure that the

margins are within your printer's ‘printable area’.

These depend upon the make and model of your

printer.

Headers and footers

Select the Page Setup window, then the

Header/Footer tab like this.

To set up the

header, select the Custom Header…

button.

Make sure that the

Align with page margins

box is ticked. The header details can be entered into

the left, centre or right sections by clicking in the

chosen section. Text can be typed; the left button

allows you to edit the text style, or automated fields

can be included. These are selected using these

buttons to insert the:

» page number

» number of pages

» date

» time

» filename with file path

» filename (no path)

» worksheet name.

153

8.1 Create a spreadsheet

You may need to produce a document that fits

on a single page (or on a specified number of

pages). Before preparing your printout (or print

preview if exporting into a document) make

sure that all data is fully visible within each

spreadsheet column and that there is no extra

space in the column that can be removed. This is

essential for both formulae and values printouts.

To fit to a single page, open the

Page Setup

window and select the Page tab. The Scaling

group is in the middle of this window. Click on

the radio button for

Fit to: like this.

The number of pages can be changed if

required. For a single page select 1 page wide by

1 tall, then click on

Displaying gridlines, row and column headings

It is sometimes easier to work on your printed/exported spreadsheet with

gridlines and row and column headings visible; it makes checking your formulae

much easier.

Both of these options are in the

Page Layout tab and can be found in the Sheet

Options

group. Click to place a tick in the relevant Print box.

Task 8b

Open the file Salary.csv in your spreadsheet.

UsinganA4pagesize,placeappropriatetextintheheadertodisplaywho

created the spreadsheet and automated data to show when it was created. Place

thefilenameandpagenumberinginthefooter of the page. Place the contents

of the spreadsheet on two pages with gridlines. Display all row and column

headings. Save the spreadsheet as Task_8b.

Open the file Salary.csv in your spreadsheet. Before starting work, examine the

data carefully. You will notice that not all data is visible. Select all the data using

the

<Ctrl> and <A> keys. An alternative method is to double-click here.

Double-click on the drag handle between the columns to resize all columns to

match the data present.

All the data should now be visible.

154

8 SPREADSHEETS

Advice

Ensureyoushowallthecontentsofacell(especiallyformulae).

Check that the paper size is set to A4. If not,

edit the settings to make it the correct size. As

all work should be of a professional standard, it

is not enough to just type your name and select

automated fields for the header. Consider what

information is required, such as your name

and when created. There are two required

elements, so to improve layout we will use the

left and right sections of the header to give the

page balance. In the left section of the header

enter text like ‘Created by’ followed by your

name. On the right, enter the text ‘Created

on’ followed by today’s date, then the text ‘at’,

followed by the time. Use the automated field

buttons for this data.

The design of the header in the spreadsheet may look like this.

The print preview for the page will appear like this.

155

8.1 Create a spreadsheet

For the footer, again there are two required elements so use text and automated

field buttons to add the filename and page numbering into the

footer like this:

Tick both Print boxes to add gridlines and

row and column headings to the sheet. The

page now looks unbalanced, with the data

to the left and lots of white space to the

right.

156

8 SPREADSHEETS

Increasing the left and right margin sizes will help but

be careful to make sure that the left and right contents

in the header and footer do not run together. In this

case setting the margins to 5 cm works well. Because

there is a large area of white space at the bottom of

page 2, increasing the top and bottom margins will also

improve the layout. Use trial and error to set these so

that there is little extra white space but the spreadsheet

fits on two pages. Setting the top and bottom to 3.6 cm

works well. You can now change the header and footer

position to 2 cm to place these vertically in the middle

of the white space at the top and bottom. The page now

looks like this.

Save your spreadsheet with the filename Task_8b.

Activity 8b

Open the file Employees.csv in your spreadsheet.

Displaytheautomatedfilenamewithitsfullfilepath

intheheaderofthepage.Placeappropriatetextinthe

footer to display who created the spreadsheet. Display

thecontentsofthespreadsheetonasingleA4page

with gridlines. Do not display row and column headings.

SavethespreadsheetasActivity_8b.

Edit spreadsheet structures

Inserting cells, rows and columns

To insert a cell into a spreadsheet, click the right mouse button on the

spreadsheet where you wish to insert the cell. From the drop-down menu

select

Insert… which opens the Insert window.

This will allow you to insert a cell (by selecting one of the top two options

which create the space for the cell by moving all other cells to the right or

down, depending on your choice), or allow you to insert a row or a column.

Deleting cells, rows and columns

To delete a cell from a spreadsheet, right click on the cell you wish to

delete. From the drop-down menu select

Delete… which opens the

Delete window.

This will allow you to delete a cell (by selecting one of the top two options

which remove the cell by moving all other cells to the left or up) or allow you

to delete a row or a column.

Resizing rows and columns

You have already learnt how to resize all columns to fit the data, but

sometimes rows and columns need manually adjusting. To manually

adjust a column width (rows are the same process), click the left

mouse button on the drag handle at the end of the column heading

and hold it down like this.

157

8.1 Create a spreadsheet

Drag the column to the required width. If you need to set a number of columns

to the same width, click and drag the column headings to highlight all those

that you wish to change. Click on one of the drag handles in this range and

resize that one column. All other highlighted columns will match its width

when you release the mouse button.

Hide and unhide rows and columns

Select the row/s or column/s that you wish to hide, using the methods

described above, then click the right mouse button in the highlighted row/

column heading to get a drop-down menu like this.

Click on the option to

Hide this row/column. To unhide, select the rows/

columns surrounding the hidden cells and use the Unhide option from this

menu. Please note that you can also Insert or Delete row/s or column/s using

this method.

Task 8c

Use the file TPAS.csv to create a spreadsheet that looks

like this:

SavethespreadsheetasTask_8c.

Advice

TorepeatthelastoperationinMicrosoft Windows,press<Ctrl> and <Y>.Toundo

thelastoperation,use<Ctrl> and <Z>.

Merging cells

For this task, you will need to merge cells A1 to C1 first and

then cells A3 to C3. Highlight cells A1 to C1 and select the

Home tab. In the Alignment group, click on the Merge &

Center icon.

Repeat this process for cells A1 to A3. Highlight the

spreadsheet and resize all columns to fit the displayed data.

Wrapping text

Change the font size in the merged cell in row 1 to 24 point. The text may no

longer fit the available space, if so (with this cell selected) click on the

Wrap

Text icon.

Use the drag handle to increase the row height, so that the text appears as

shown in the question. Repeat this process to wrap the text in cell B5; you may

need to adjust both row height and column width to make the cell appear like

in the question. Highlight cells A5 to C5 and select the middle button for the

vertical alignment like this.

158

8 SPREADSHEETS

Right align all cells between A6 and C17, then centre align all cells between B6

and B17. Select row 2 and reduce the height of this row to half the height of

row 3. Repeat this for row 4. Check that your finished spreadsheet matches the

diagram in Task 8b. Save your spreadsheet with the filename Task_8c.

Activity 8c

Createaspreadsheetthatlookslikethis:

Placeyournameandthefilenameintheheader.Savethespreadsheetas

Activity_8c.

Protecting cells and their contents

You can protect parts of the spreadsheet to stop anyone changing, deleting

or moving important data within the sheet. This can involve protecting cells

within a worksheet, a worksheet within a multi-layered workbook, or the

entire workbook.

Advice

MakesurethatyouhavereadSection1.4Checkingtheaccuracyofdatabefore

attempting this Task.

Task 8d

OpenthefilethatyousavedasTask_8c. Protect all cells between A1 and C5 and

betweenA6andA17inclusive.UsethepasswordHodder to protect your sheet.

SavethespreadsheetasTask_8d.

Identify and highlight the individual cell or cells that you will

protect, in this case the first range is between A1 and C5. Select

the

Home tab and the drop-down menu for the Alignment

group.

159

8.1 Create a spreadsheet

This opens the Format Cells window. Select the Protection tab. Make sure

that the tick box for Locked contains a tick, before clicking on

. Repeat

this process for cells A6 to A17. Make sure that the other cells B6 to C17 do not

have the tick box for Locked displayed.

To activate protection, move to the

Review tab. In the Protect group, select

Protect Sheet.

This opens the Protect Sheet window. Because the locked and

unlocked rangesof cells have been defined, Excel has selected the

first two tick boxes. Any tick boxes that are not selected mean

that those functions are removed when the sheet is protected, for

example the formatting of any cells in the sheet will not be allowed

(all the options to format will be ‘greyed out’ from the menu).

Type the password, in this case ‘Hodder’ into the box and press

. You will need to re-enter this password and click

for double-entry verification by Excel.

The same method can be used to protect whole rows or columns,

by selecting the rows or columns rather than a range of cells.

Protecting the workbook protects only the structure of the

workbook, to prevent sheets being added to it or deleted from it.

It is accessed using the

Protect Workbook icon.

To unprotect the sheet and edit these cells, use the

Review tab,

and in the Protect group select Unprotect Sheet. You must enter

the password followed by

Activity 8d

OpenthefilethatyousavedasActivity_8a. Protect all cells in the Sites sheet

and cells A2 to C18 in the Colour sheet so that they cannot be edited. Allow all

cells in the TMC sheet to be edited. Use your name as the password to protect

your workbook. Save the spreadsheet as Activity_8d.

Advice

Passwordscanberemovedbyselecting

the File tab, the Info section, then using

the

Unprotect hyperlinks for each sheet,

or for the workbook, by double-clicking

on the drop-down menu for Protect

Workbook.

Split window and freeze panes

It is sometimes easier to work on a large spreadsheet by being able to see two

different parts of the same sheet on the screen at the same time. Two methods

that can be used for this are:

» split the window

» freeze panes within the window.

Split windows

Windows can be split horizontally, vertically or both. To

split a window horizontally, select the row directly below

the position you require the split. Select from the

View

tab, in the Window group, the Split button.

160

8 SPREADSHEETS

To remove the split window, click the Split button again.

To split the window vertically, select the column after the position you require

the split, then click the

Split button.

To split the window both horizontally and vertically, select the cell below and to

the right of the required split position, then click the

Split button.

Task 8e

OpenandexaminethefileStock.csv. Split this so that both types of stock can be

viewed together. Save the spreadsheet as Task_8e.

‘Open and examine the file.’ Before

starting work you need to understand

the contents and structure of the files

given. In this case the Stock file is split

into two different parts; the top part

for motherboards and the lower part for

processors. The split must appear below

row 120 and above row 121. Highlight

row 121. Select from the

View tab, in the

Window group, the Split button. You can

now scroll independently in both windows.

Scroll up in the top window so the window

looks like this.

Save the spreadsheet as Task_8e.

Task 8f

OpenandexaminethefileMotherboard.csv. Freeze the panes so that the

partnumberandstockcolumnsandrows1to4arealwaysvisible.Savethe

spreadsheet as Task_8f.

Freeze and unfreeze panes

Freezing a pane works in the same way as a split window, but it will stop part of

the sheet from scrolling. Open and examine the file Motherboard.csv. Move

the cursor to cell C5, which is the first cell that will be allowed to scroll. Select

from the

View tab, in the Window group, the drop-down menu from the

Freeze Panes button.

Click on the Freeze Panes

button; now rows 1 to 4 and

columns A and B will be frozen

(fixed) within the window,

so the spreadsheet looks like

this:

161

8.1 Create a spreadsheet

To unfreeze the panes, select the drop-down menu from the Freeze Panes

button, then the Unfreeze Panes option.

8.1.2 Create formulae and use functions

Display formulae

Formulae are always preceded by an = sign when entered into a spreadsheet. The

formula held in the current cell is displayed in the Formula bar, along with the

address of that cell like this.

To display all formulae in

your spreadsheet, select the

Formulas tab, in the Formula Auditing group, select the

icon.

This button toggles to switch on and off the formulae view, which may be

required to give evidence of your methods.

Create formulae

Mathematical operators

Simple mathematical operators are placed in a formula and can be used to

add, subtract, multiply, divide and calculate indices (powers) of a number. For

addition, use the + symbol; for subtraction, use the - symbol; for multiplication,

use the * symbol; and for division, use the / symbol. Indices are calculated

using the ^ symbol, so the contents of cell A2 squared (x

) would be typed as

=A2^2

Task 8g

OpenandexaminethefileOperators.csv. Place two numbers of your choice in

cells B2 and B3. Calculate in cell:

» B5,thesumofthetwonumbers

» B6,thedifferencebetweenthetwonumbers

» B7,theproductofthetwonumbers

» B8, the contents of cell B2 divided by the contents of cell B3

» B9,thecontentsofcellB2tothepowerofthecontentsofcellB3.

Check that the formulae have worked. Save the spreadsheet as Task_8g.

Open the file Operators.csv. Enter the number 4 in cell B2 and 2 into cell B3.

The values were carefully selected to make the maths easy when you check your

calculations. The more difficult calculations are likely to be the division and

indices. These numbers were selected so that the 4 divided by 2 gives an easy

result and the 4 to the power of 2 is reasonably easy (4×4).

It is wise to perform all calculations by hand before entering the formulae, to

make sure that you understand the formulae that you are using and to find the

results of the calculation before the computer has shown you its results. These

calculations may look like this.

» Addition: Enter the formula =B2+B3 in cell B5.

» Subtraction: Enter the formula =B2-B3 in cell B6.

» Multiplication: Enter the formula =B2*B3 in cell B7.

» Division: Enter the formula =B2/B3 in cell B8.

» Indices: Enter the formula =B2^B3 in cell B9.

162

8 SPREADSHEETS

Advice

WhileotherfunctionslikeSUM,PRODUCTorPOWERcouldalsobe

used to perform these tasks, using mathematical operators provides

the most efficient method.

To check that the formulae have worked correctly, compare your

original paper-based calculations above with the values in the

spreadsheet.

Save the spreadsheet as Task_8g.

Indices

Task 8h

Createaspreadsheettocalculatex

whenyouaregiventhevalueofxandn.

Usethisspreadsheettocalculate3

, π

,16

-2

and 16

0.5

.Savethespreadsheetas

Task_8h.

Indices are sometimes called powers. They are commonly used in

mathematical and scientific formulae. To calculate values like 3

or x

, a

spreadsheet provides you with the ideal tool. You could calculate 3

multiplying (=3*3*3*3) or with the formula =3^4. However, this will not

allow these figures to be changed without changing the formula. A better

solution is to create a small spreadsheet model like this.

By changing the values in B1 and B2, we can get different answers that will be

shown in cell B4. To calculate 3

, place 4 in B1 and 3 in B2 which will display

81 in cell B4. To calculate π

, place 2 in B1 and 3.14 (the value of Pi rounded to

two decimal places) in cell B2, which will display 9.8596 in cell B4.

Advice

Whenwelearntousefunctionslaterinthischapter,wecanreplace3.14witha

Pi (π)function.

To calculate 16

-2

, place -2 in B1 and 16 in cell B2, which will display

0.00390625 in cell B4. To calculate 16

0.5

, place 0.5 in B1 and 16 in cell B2,

which will display 4 in cell B4. You can see that 16

0.5

is the same as 16.

Save your spreadsheet as Task_8h.

Activity 8e

Create a spreadsheet to calculate x

foranygivenvalues.Usethisspreadsheetto

calculate 4

,24

,0.7

−3

, x

where x=3.7. Save the spreadsheet as Activity_8e.

Absolute and relative cell referencing

An absolute reference is a cell reference that will not

change when a formula is replicated. All or part of a cell

reference can be made absolute by adding a $ sign in

front of the part to ‘fix’ in place. A

relative reference

is a cell reference that will change when a formula

is replicated. This spreadsheet shows some examples

where formulae in cells A2, F2, A10 and F10 have been

replicated in both directions:

163

8.1 Create a spreadsheet

Named ranges (met later in this chapter) are another example of absolute

referencing.

Using absolute and relative cell references

Task 8i

Createaspreadsheettocalculatethetimestables.CellA1shouldcontainthe

number of the table and the table should be calculated between 1 and 10. Save

the spreadsheet as Task_8i.

Create a new spreadsheet to look like this (in formula

view).

Highlight cells A3 and A4 as shown, then use the

drag handle in cell A4 to drag the numbers down

from A4 to cell A12.

In cell B3 the formula is

=A3*$A$1 which contains both absolute and relative

referencing. The reference to cell A1 (with the $ symbols) is an absolute

reference and the reference to cell A3 is a relative reference.

Select B3 and use the drag handle to replicate (copy) this formula down into

cells B4 to B12. You can see from this view that the reference in cell B3 to cell

A3 has been changed as the cell has been replicated because it uses relative

referencing. However, the reference to cell A1 has not been changed during the

replication because absolute referencing has been used.

To return to the values view of the spreadsheet, select the

Formulas tab, in

the Formula Auditing group, then select the

icon. Save your

spreadsheet as Task_8i.

Named cells and ranges

It is difficult to remember which cell holds which data, especially in a large

spreadsheet. When an individual cell or an area of a spreadsheet is going to be

referred to many times within spreadsheet formulae, it is often a good idea to

give it a name. This name should be short and meaningful. In the case of a large

spreadsheet, it is easier to remember the name of a cell, for example VAT or

AveMiles, rather than trying to remember the cell reference, for example AC456

or X232. Once a cell or a range of cells has been named, you use this name in

all your formulae within that workbook.

Task 8j

Open the file TBCWorkers.csv.Formatthe

spreadsheet as shown.

Name cell B2 Pay,cellE2Tax and cells B6 to C11

Hours. Save the spreadsheet as Task _8j.

164

8 SPREADSHEETS

Open the file and find cell B2. You must name this cell ‘Pay’.

Right click the mouse in this cell to get the drop-down

menu and select

Define Name… to open the New Name

window. In the Name: box, overtype the suggested name

with the word

Pay. Add suitable text in the Comment: box so

that the window looks like this.

Repeat this to name cell E2 Tax.

To create the named range called ‘Hours’, you must highlight

the cells between B6 and C11. Click the right mouse button

within the highlighted range to get the drop-down menu.

Enter the text

Hours into the Name: box and add an

appropriate

Comment:. Check that the New Name window

looks like this before clicking on

The name of the range is only visible in the

Name box if all

the cells in the range are highlighted.

This named cell and range will be used in later tasks.

Save this spreadsheet as Task_8j.

If you have already created a named cell or range and wish

to view it or edit it, you can open the

Formulas tab, in the

Defined Names group select the Name Manager.

The

Name Manager looks

like this.

Double-click on the name

that you wish to view or edit.

Use functions

Function names

A function has a predefined

name like

SUM or AV ER AGE

that performs a particular

calculation. It is an operation

built into the spreadsheet.

There are lots of these

functions in Excel, many of which are beyond the scope of this book, but each

has a reserved function name. If a question asks you to choose your own name

for a cell or range, try to avoid using these function names.

165

8.1 Create a spreadsheet

SUM function

The

SUM function adds two or more numbers together. In Task 8g, you used

the mathematical + operator and the formula

=B2+B3 to add the contents of

two cells together. With only two cells to be added, this was the most efficient

way of doing this. If there had been more figures to add, particularly if they

were grouped together in the spreadsheet, using the SUM function would have

been more efficient.

Task 8k

OpenthespreadsheetyousavedasTask_8j.Usefunctionstocalculateinthe

appropriate cells the:

» totalnumberofhoursworkedeachweek

» total hours worked by each person

» total hours worked.

CalculateincellsB13andC13theaveragenumberofhoursworkedbyan

employee that week. Calculate in cell B14 the largest and in cell B15 the least

numberofhoursworkedeachweek.Addappropriatelabelstothisdata.

SavethespreadsheetasTask_8k.

Advice

Whenreadingthequestionstem,youwillnoticethatitstates:‘Usefunctions

to calculate …’. This means that, apart from its inefficiency, you cannot use a

formula like =B6+B7+B8+B9+B10+B11 as this does not contain a function. If a

question stem states ‘Place a formula …’ then it is fine to use a function within

the formula.

Advice

Analternativemethodistoenter=SUM( then drag the cursor to highlight cells B6

to B11, enter ) and press the <Enter> key.

Open the spreadsheet. In cell B12 enter the formula =SUM(B6:B11). This

should give the value 44. Replicate this formula (using the drag handle into

cell C12). This should place the formula

=SUM(C6:C11) in this cell and give

the value 43. In cell D6 enter the formula =SUM(B6:C6) and replicate this

formula down to cell D12. In this case we have used the SUM function rather

than the formula =B6+C6 because it is likely that extra weeks may be added into

the spreadsheet, in which case the SUM function would be more efficient. The

value in cell D12 should be 87.

We will now try two alternative SUM functions in cell D12 that should also

return the value 87. These are:

=SUM(B12:C12) and =SUM(Hours). This

works because in Task 8j we gave the name ‘Hours’ to all the cells in the range

B6:C11.

Advice

AnalternativewaytousetheSUMfunctionwithouttypingisforyouto

use AutoSum. Move the cursor into the required cell, select the Home tab

and find the Editing group. Click on the AutoSum icon.

This will place the SUM function into the current cell and Excel will attempt

to work out which cells to use; if they are not correct, drag the cursor to

highlight the required cells.

166

8 SPREADSHEETS

There are many ways of using the SUM function, some of which are shown in

Table 8.1.

Function Equivalent formula What it does

=SUM(B4:B8) =B4+B5+B6+B7+B8 Adds up the contents of all the cells in

the range B4 to B8

=SUM(D3,D8,D12) =D3+D8+D12 Adds up the contents of the cells D3, D8

and D12

=SUM(D5:D8,F2) =D5+D6+D7+D8+F2 Adds up the contents of the cells in

therange D5 to D8 and the contents

of cell F2

=SUM(MyRange) None Adds up the contents of all the cells

within a named range, in this case called

MyRange

▲ Table 8.1 Ways of using the SUM function

AVERAGE function

To find the average number of hours worked, click the cursor into cell B13

and use the

AV ER AGE function to calculate the mean (average) of a list of

numbers. Enter the formula =AVERAGE(B6:B11). This should give the value

7.333333. Replicate this function into cell C13 and add an appropriate label

(like Average hours) in cell A13.

There are many ways of using the AVER AGE function, some of which are

shown in Table 8.2.

Function Equivalent formula What it does

=AVERAGE(B4:B8) =(B4+B5+B6+B7+B8)/5 Calculates the mean of the cells in

the range B4 to B8

=AVERAGE(D3,D8,D12) =(D3+D8+D12)/3 Calculates the mean of the cells

D3, D8 and D12

=AVERAGE(D5:D8,F2) =(D5+D6+D7+D8+F2)/5 Calculates the mean of the cells in

the range D5 to D8 and cell F2

=AVERAGE(MyRange) None Calculates the mean of the cells in

a named range called MyRange

▲ Table 8.2 Ways of using the AVERAGE function

MAX function

MAX is a function used to select the largest (maximum) value within a list of

numbers. To calculate the largest number of hours worked over the two-week

period, click in cell B14 and enter

=MAX(Hours). This uses the named range

‘Hours’ and returns the value 14.

MIN function

MIN is a function used to select the smallest (minimum) value from a list. To

calculate the least hours worked, select cell B15 and enter =MIN(Hours). This

uses the named range ‘Hours’ and returns the value 2. Save this spreadsheet as

Task_8k.

167

8.1 Create a spreadsheet

Activity 8f

Open the file January.csv as a

spreadsheet. Place your name and

filename in the header.

Insert new rows between rows 2

and 3 and between rows 1 and 2.

Format the top of the spreadsheet

to look like this.

Enter functions in cells B16 to B19 to calculate, using a named range, the total

number of days worked by all employees, the average number of days worked,

and the maximum and minimum values.

SavethespreadsheetasActivity_8f.

Whole numbers and rounding

INT function

In mathematics, the word ‘integer’ is used to describe a whole number (with

no decimals or fractions). In Excel, the INT function takes the whole number

part of a number and ignores all digits after the decimal point, also known as

truncating. Open a new spreadsheet and enter 84.56789 into cell A1. Enter

in cell A2 the formula

=INT(A1) which will return the value 84. Change the

value in cell A1 to see the changing results in cell A2.

Advice

Settingacellasanintegervaluewilltruncatethecontentsofacelltoremovethe

decimal/fraction part of the number. This is not the same as formatting a cell to

0 decimal places, which stops the decimal/fraction part from being displayed but

notfrombeingusedinacalculation.

Rounding

Often a spreadsheet produces an extremely accurate answer, with more digits

than we really need. Rounding involves taking a number and making it shorter,

although it is still approximately equal to the original. This new value stored

may be slightly more or slightly less than the original. For example: if a cell

holds the value $23.486532, we may only need it to contain the number of

dollars and cents. It is therefore a good idea to shorten and store this value to

two decimal places to make it more meaningful. This is called rounding.

ROUND function

Task 8l

OpenthefileyousavedasTask _8k. Insert a new row between rows 13 and 14.

Copy the contents of cell A13 into cell A14. In cells B14 and C14, round the

corresponding values in row 13 to one decimal place. Hide row 13. Save the

spreadsheet as Task_8l.

168

8 SPREADSHEETS

Open Task_8k. Insert the new row between rows 13 and 14. Move the cursor

into cell A14 and enter the forumla =A13. This takes the contents of A13 and

copies it into A14. This function would allow the contents of A13 to change

and A14 will change with it rather than the repeated use of copy and paste when

cells are edited.

In cell B14, enter the formula

=ROUND(B13,1). This formula uses the ROUND

function, which takes the content of cell B13 and rounds the number to one

decimal place. Replicate this function into cell C14 using the drag handle. If

the next digit is five or more, the number will be increased by one. For example

in cell B13 the value is 7.3333333, so the content of B14 is 7.3 but in cell C13

the value is 7.166667, so the content of C14 is 7.2 as it has rounded up the first

decimal place because the second

was a 6. Hide (do not delete) row 13.

Save the spreadsheet as Task_8l.

Rounding to zero digits is the

same as using the INT function.

Rounding with a positive number

of digits rounds to that number of

decimal places. Rounding with a

negative number of digits rounds

to the nearest 10, 100 and so on, as

shown in Table 8.3.

Advice

Roundingacellwillremovepartofthenumber.Thisisnotthesameas

formatting a cell (which will not remove part of the number). Rounding will affect

any calculations based upon this cell.

ROUNDUP function

The ROUNDUP function behaves like ROUND, except that it always rounds a

number up.

Task 8m

Createanewspreadsheetlikethis:

Usethisspreadsheettocalculatethe

number of packs of bricks required to

build a wall requiring 1100 bricks. Bricks

canonlybeboughtinwholepacks

of 390. Save the spreadsheet as Task_8m.

Open a new spreadsheet and create the spreadsheet as shown. Enter the value

1100 in cell B2. Enter in cell B4 the formula =B2/B3 which returns the value

2.820513. In order to calculate the number of whole packs of bricks (as we

cannot buy 0.820513 of a pack), we can use the ROUNDUP function. Edit

the formula in B4 so that it becomes

=ROUNDUP(B2/B3,0) which rounds

up to 0 decimal places (a whole number) and now returns a value of 3. Save the

spreadsheet as Task_8m.

ROUNDDOWN function

The

ROUNDDOWN function behaves like ROUND and ROUNDUP, except

that it always rounds a number down (this is also called truncating a number).

Number to be rounded 10928.375

=ROUND($B$1,3) 10928.375

=ROUND($B$1,2) 10928.38

=ROUND($B$1,1) 10928.4

=ROUND($B$1,0) 10928

=ROUND($B$1,-1) 10930

=ROUND($B$1,-2) 10900

=ROUND($B$1,-3) 11000

▲ Table 8.3 Effects of using the ROUND function

169

8.1 Create a spreadsheet

Task 8n

Createanewspreadsheetlikethis:

Usethisspreadsheettocalculatethe

valueofPi(π)truncatedtoanumber

of decimal places.

SavethespreadsheetasTask_8n.

Open a new spreadsheet and create the spreadsheet as shown. Enter =PI( ) in

cell B2, which is the function to display the value of Pi (π). Enter the value 3 in

cell B3. Enter in cell B4 the formula =ROUNDDOWN(B2,B3) which returns

the value 3.141. Change the value in cell B3 to truncate Pi to a different number

of decimal places. Save the spreadsheet as Task_8n.

Activity 8g

Open the file Rounding.csv in your

spreadsheet. Edit it to look like this.

EnteraformulaincellB3tocalculatethe

whole number part of cell B2.

EnterformulaeincellsB8toB10toround,

round up and round down the contents of

cell B6 to the number of decimal places

specified in cell B7.

EnterformulaeincellsB16toB26toroundthecontentsofcellB13tothe

number of decimal places specified in the corresponding cell in column A.

For each function entered in column B, place the name of the function in the

correspondingcellincolumnC.Trydifferentvaluesinthisspreadsheettosee

the effects of the different functions. Save the spreadsheet as Activity_8g.

Counting

There are two main functions used to count the number of data items in a

spreadsheet and two conditional counting functions.

Task 8o

Open the file Hotels.csv andformatittolook

like this.

Place, in the appropriate cells, calculations

for the number of hotels and, for each room

type, the number of:

» rooms available

» hotels with available rooms

» hotels without available rooms.

SavethespreadsheetasTask_8o.

170

8 SPREADSHEETS

Open the spreadsheet and format it as shown in the Task. To calculate

the number of rooms of each type available, place in cell B20 the formula

=SUM(B5:B18) which will return the value 47. Use the drag handle to

replicate this into cells C20 and D20.

COUNT function

COUNT looks at the cells within a given range and counts the number of these

cells containing numeric data. To calculate the number of hotels with rooms

of each type available, you must count the number of numeric cells. Place the

cursor in cell B21 and enter the formula

=COUNT(B5:B18) which will return

the value 10. Use the drag handle to replicate this into cells C21 and D21. Note

that the COUNT function ignores any cells containing labels or blank cells.

COUNTA function

The

COUNTA function works in a similar way to the COUNT function.

Rather than counting just the number of numeric values, this function counts

the number of cells that display either a numeric value or text. It will not count

any blank cells within the range. To calculate the number of hotels, move the

cursor into cell B23. Enter the formula

=COUNTA(B5:B18) which will return

the value 14. It is not necessary to replicate this function as this data will not

change for different room types.

In Excel, there is not a count function for just text values, so the COUNTA

and COUNT functions will both be used to calculate the number of

hotels with no room of this type available. Enter in cell B22 the formula

=COUNTA(B5:B18)-COUNT(B5:B18) which will return the value 4. Use

the drag handle to replicate this into cells C22 and D22.

Activity 8h

Open the file Class.csv. This spreadsheet

lists all the students in a year, with their class

and their grades in Maths and English.

Format the top of the spreadsheet and each

person’s Grades box to look like this.

Format the bottom of the spreadsheet to look

like this.

PlaceaformulaincellC102tocountthe

number of students in the class. Place a

formulaincellC103tocountthenumberof

grades gained by the whole year in both Maths

and English.

FitthespreadsheetsothatitwouldprintontwoA4pages.

SavethespreadsheetasActivity_8h.

Nested functions

Nested functions involve placing one function inside another one. Sometimes a

formula can contain several other functions nested within each other.

171

8.1 Create a spreadsheet

Task 8p

OpenthefileyousavedasTask _8k.

Edittheformulaeinrow13tocalculatetheaveragenumberofhoursworked

eachweek,roundedtoonedecimalplace.

SavethespreadsheetasTask_8p.

Study your solution to Task 8l, where you inserted a row, rounded the contents

of the average calculation, then hid the row containing the unrounded data.

Open your solution to Task 8k. Edit the formula in cell B13 so that the

AVERAGE function is nested within the ROUND function like this

=ROUND(AVER AGE(B6:B11),1).

This is a much more efficient solution than Task 8l and calculates the average

value first, then rounds this to one decimal place to give the value 7.3. Replicate

this function into cell C13. Save the spreadsheet as Task_8p.

Activity 8i

OpenthefileyousavedasActivity_8f.

Edit the formula in cell B17 to round the calculated average to two decimal

places. Save the spreadsheet as Activity_8i.

Conditional functions

Conditional functions all test a condition and then return different answers or

perform different operations depending on the value of that condition.

IF function

The

IF function is the most commonly used conditional function. It looks at a

given condition and performs an operation if the condition is met, or a different

operation if the condition is not met. The operation could be to place a value or

label in the cell, it could involve a reference to another cell, or it could involve a

more complex calculation.

An IF function contains a pair of brackets and within the brackets three

parts, each separated by a comma. An example of an IF function is

=IF(A6=4,B6*0.04,"No discount offered"). The first part is a condition; in

this example, it is testing to see if cell A6 contains the number 4. The other two

parts are what to do if the condition is met and what to do if it is not met. In

this example, if the condition is met, the contents of cell B6 will be multiplied

by 0.04 and the answer will be displayed. If the condition is not met, ‘No

discount offered’ will be displayed.

Task 8q

OpenthefilesavedasTask_8o.

AddanewlabelFamily availability in cell E3. Place formulae in cells E5 to E18

to display Family rooms available or Not available dependinguponthecontents

of column D.

SavethespreadsheetasTask_8q.

172

8 SPREADSHEETS

Open the file and place the cursor in cell E3. Enter the label ‘Family

availability’. Place the cursor in cell E5 and enter the formula

=IF(D5="None","Not available","Family rooms available").

Do not use absolute referencing in this formula as the reference to cell D5 needs

to change when you replicate the formula. Replicate this formula so it is copied

into cells E5 to E18. Save your spreadsheet as Task_8q.

There are many ways of using the IF function, some of which are shown in

Table 8.4.

In a spreadsheet we wish to enter formulae into cell G1.

Function What it does

=IF(B6="C","Car","Not a car")

Checks the contents of cell B6. If the contents match the letter "C" with

no additional spaces, etc., then cell G1 displays "Car". If cell B6 contains

anything other than the letter "C", then cell G1 displays "Not a car".

=IF(B7=3,"March","Not March")

Checks the contents of cell B7. If the contents are the value 3, then cell G1

displays "March". If cell B7 contains anything other than the value 3, then

cell G1 displays "Not March".

=IF(B7<4,"1st Quarter","")

Checks the contents of cell B7. If the contents are less than the value 4,

then cell G1 displays "1st Quarter". If cell B7 contains a value of 4 or more,

then cell G1 displays a null string (no contents are shown).

=IF(B7<=3,"1st Quarter","")

Checks the contents of cell B7. If the contents are less than or equal to 3,

then cell G1 displays "1st Quarter"; if not, cell G1 displays a null string.

=IF(OR(B7=4,B7=5,B7=6),"2nd

Quarter","")

Checks the contents of cell B7. If the contents are any of the values 4, 5 or

6, cell G1 displays "2nd Quarter"; if not, cell G1 displays a null string.

=IF(AND(B7>3,B7<7),"2nd Quarter","")

Checks the contents of cell B7. If the contents are greater than 3 AND less

than 7, cell G1 displays "2nd Quarter"; if not, cell G1 displays a null string.

=IF(NOT(OR(B7<4,B7>6)),"2nd

Quarter","")

Checks the contents of cell B7. If the contents are NOT less than 4 OR NOT

greater than 6, cell G1 displays "2nd Quarter"; if not, cell G1 displays a null

string.

▲ Table 8.4 Ways of using the IF function

IFS function

The IFS function looks at a number of given conditions and performs a

different operation if one of these conditions is met. If none of the conditions

are met it returns an error message, so careful use of error trapping may be

needed with this function (we will look at error trapping later in this Task). This

is just a more efficient method of nesting IF statements.

Task 8r

Open the file WorkLocation.csv andforeachemployee,incolumnC,identify

where they are working, given that project Alpha is in London, project Beta is in

Milan and project Gamma is in Tunis.

SavethespreadsheetasTask_8r.

Open the file and place the cursor in cell C2. Enter the formula:

=IFS(B2="Alpha","London",B2="Beta","Milan",B2="Gamma","Tunis")

Do not use absolute referencing in this formula as the references to cell B2 need

to change when you replicate the formula. Replicate this formula so it is copied

into cells C3 to C11.

173

8.1 Create a spreadsheet

Error trapping

ISERROR function

The ISERROR function is used to detect if an error will occur when a formula

is used, and returns True if there is an error or False otherwise. Although this

can be used to detect errors, there is a better alternative that detects the error

and allows us to trap the error so that it does not appear as Excel would generate

it. The IFERROR function can be used to indicate the error or in some cases

correct the error.

IFERROR function

When this formula is replicated an error occurs in cells C10 and C11 as the

three conditions in the IFS function are not met. Rather than allow Excel

to display this error, in this case as #N/A, we can add error trapping to the

function. For this we will include an IFERROR function in each of the

replicated cells. Move the cursor into cell C2 and edit the formula so that

it is:

=IFERROR(IFS(B2="Alpha","London",B2="Beta","Milan",B2

="Gamma","Tunis"),"Project location unknown")

This has used the original IFS function, highlighted in yellow, and placed this

within an error trapping routine. Replicate this formula so it is copied into cells

C3 to C11. Rather than the generated #N/A error message, cells C10 and C11

now display ‘Project location unknown’.

Save your spreadsheet as Task_8r.

Conditional functions

There are many conditional functions that can be used in Excel. Many involve

the use of the IF (or IFS) function together with a mathematical function

like COUNT, AVERAGE, MAX, MIN. We will study some of these but will

not cover all of these functions. If you understand the structure of the IF

and IFS functions and the mathematical functions, then other functions like

COUNTIFS, MAXIF, MINIFS and so on can be used in the same way as the

examples shown.

Conditional counting

COUNTIF function

If conditional counting is required with a single condition, then COUNTIF is

the most efficient function to use.

Task 8s

OpenthefilesavedasTask_8q.IncellsB22toD22,entersinglemoreefficient

functionstocalculatethenumberofhotelswithnoroomofthattypeavailable.

SavethespreadsheetasTask_8s.

Open the file saved as Task_8q. The current method counted the number

of cells containing any data or label, then subtracted the number of cells that

contain a number. It is more efficient to use a single COUNTIF function

which will count the number of times a condition is met. Place the cursor in

cell B22 and enter

=COUNTIF(B5:B18,"None"). This function will check

each cell in the range B5 to B18 and will only count those that contain the

text/string "None". Replicate this function into cells C22 and D22 using

174

8 SPREADSHEETS

the drag handle. Save your spreadsheet as Task_8s. The formula/method

used in Task 8q would be valid but at AS level you would need to have used

COUNTIF as the use of the most efficient formula/method is essential.

Activity 8j

OpenthefilethatyousavedasActivity_8h. This spreadsheet lists all the

studentsinaclass.Nexttoeachstudent’snameisthenameoftheclassthat

they are in.

Place formulae in cells G2 to G5 that use both absolute and relative referencing

to count the number of students in each class.

SettheprintareaofthespreadsheettoshowcellsF1toG11only.

SavethespreadsheetasActivity_8j.

COUNTIFS function

If conditional counting is required with two or more conditions, then the

COUNTIFS function offers us the most efficient solution.

Task 8t

OpenthefilesavedasTask_8s. Enter in cell A24 the text Number of hotels with

no available rooms.

Applyappropriateformattingtothistext.EnterincellB2afunctiontoperform

this calculation. Edit the data as five double rooms have now been booked at the

Kaiserhof Hotel.

SavethespreadsheetasTask_8t.

Open the file saved as Task_8s. Enter into cell A24 the label ‘Number

of hotels with no available rooms’. Use the format painter tool to format

this text to match cells A5 to A23. Enter into cell B24 the formula

=COUNTIFS(B5:B18,"None",C5:C18,"None",D5:D18,"None"), which will

check the first cell in the range B5 to B18 and if it contains the string "None",

will move on to check the cell in the same row within the range C5 to C18.

If this cell also contains the string "None", it will then check in the same row

within the range D5 to D18 and will only count if it also contains the string

"None", before moving on to test the next row. It does this until it reaches cell

D18. This function should return the value 0 to start with, as no hotel is full.

Follow the instruction to edit the data because five double rooms have now

been booked at the Kaiserhof Hotel by entering in cell C15 the string "None".

This will now change the resulting value in cell B24 to 1.

Activity 8k

OpenthefilethatyousavedasActivity_8j.

Enter the following text into the spreadsheet and

format it like this.

Place formulae in cells C104 to C108 to display

the relevant values.

Settheprintareaofthespreadsheettoshow

cells A104 to C108 only.

SavethespreadsheetasActivity_8k.

175

8.1 Create a spreadsheet

Task 8u

Open the file TWTWorkers.csv

inaspreadsheet.Editand

format the spreadsheet to look

like this.

Enter functions into each green

cell to calculate the required

values, rounded to the nearest

whole number.

Savethespreadsheetas

Task_8u.

Open the file and format it as shown in the question using the

skills learnt earlier in the chapter. Enter in cell B5 the formula

=COUNTIF($D$24:$D$52,A5), which counts any cells in the range

D24 to D52 that contain the same as the contents of cell A5, in this case

‘Male’. Because we have used the correct absolute and relative cell references

we can use the drag handle to replicate this down into cell B6.

Advice

In Excel,the<f4> key toggles between absolute and relative referencing. To

useit,highlightthecellorrangetobechanged(forexampleintheformula

in cell B5, highlight the range $D$24:$D$52) and press the <f4> key until the

correct referencing is found. This will toggle between $D$24:$D$52, D$24:D$52,

$D24:$D52 and D24:D52.

Conditional sum

SUMIF function

If a conditional total is required using a single condition, then the SUMIF

function offers the most efficient solution to a task.

Enter in cell B9 the formula =SUMIF($D$24:$D$52,A9,$F$24:$F$52),

which compares the contents of each cell in the range D24 to D52 with the

contents of cell A9, in this case ‘Male’. If these values or strings match, then the

value contained in the corresponding cell in the row within the range F24 to

F52 is added to the total. Use the drag handle to replicate this into cell B10.

SUMIFS function

If a conditional total is required with two or more conditions, then the

SUMIFS function offers the most efficient solution to a task.

The formulae needed for cells B14 and B15 both require two conditions to

be met. For B14 the employee has to be male and work full time. The syntax

used for SUMIFS, compared to that used for SUMIF, is very different. With

SUMIFS, the first range given will be the range containing the cells to be

added together. The next part of the function will be a range of cells to be

tested and also the first condition to be tested. Then will come another range of

cells to be tested followed by the second test condition. This is repeated for all

the conditions to be tested.

176

8 SPREADSHEETS

Our Task only requires two conditions to be met, so will consist of the range to

be summed and two further ranges and their associated criteria. Enter into cell

B14 the formula

=SUMIFS($F$24:$F$52,$D$24:$D$52,A14,$E$24:$E$52,"Full").

This adds the value of each row held within the range F24 to F52 to the total,

only if both these criteria are met:

» the contents of the cell (in this row) in the range D24 to D52 contain the

same as cell A14

» the contents of the cell (in this row) in the range E24 to E52 contain the

label "Full".

Use the drag handle to replicate this into cell B15.

Similar formulae are required in cells B19 and B20 to calculate the total

salary of part-time male and part-time female workers. Copy the formula

from cell B14 and paste it into cell B19. Edit this formula so it becomes

=SUMIFS($F$24:$F$52,$D$24:$D$52,A14,$E$24:$E$52,"Part").

Use the drag handle to replicate this into cell B20.

Conditional average

AVERAGEIF function

If a conditional mean (average) is required using a single condition, then the

AV ER AGEIF function offers the most efficient solution to a task.

Enter into cell D9 the formula

=AVERAGEIF($D$24:$D$52,A9,$F$24: $F$52), which works in a similar

way to SUMIF, except the calculated part gives the mean average rather than

the total of all cells where the condition is met. An efficient method of doing

this is to copy and paste the formula from B9, then edit the function name.

The resulting value in D9 is 18636.36364. This needs to be rounded to no

decimal places, so amend the formula to become:

=ROUND(AVERAGEIF($D$24:$D$52,A9,$F$24:$F$52),0).

Use the drag handle to replicate this into cell D10.

AVERAGEIFS function

If a conditional mean (average) is required with two or more conditions, then

the

AV ER AGEIFS function offers the most efficient solution to a task.

Copy the function from B14, paste it into D14 and edit it to become

=ROUND(AVERAGEIFS($F$24:$F$52,$D$24:$D$52,A14,

$E$24:$E$52,"Full"),0)

. Use the drag handle to replicate this into cell D15.

Similar formulae are required in cells D19 and D20 to calculate the average

salary of part-time male and part-time female workers. Copy the formula

from cell D14 and paste it into cell D19. Edit this formula so it becomes

=ROUND(AVERAGEIFS($F$24:$F$52,$D$24:$D$52,

A19,$E$24:$E$52,"Part"),0). Use the drag handle to replicate this into cell

D20.

Conditional maximum

MAXIFS function

If a conditional maximum is required with two or more conditions, then the

MAXIFS function offers the most efficient solution to a task.

177

8.1 Create a spreadsheet

Copy the function from B14, paste it into F14 and edit it to become:

=ROUND(MAXIFS($F$24:$F$52,$D$24:$D$52,A14,

$E$24:$E$52,"Full"),0)

. Use the drag handle to replicate this into cell F15.

Save the spreadsheet as Task_8u.

Activity 8l

OpenthefilethatyousavedasActivity_8k.

AddappropriatefunctionsincellsG2toG5tocountthenumberofstudentsin

each class. Set the print area of the spreadsheet to show cells F1 to G5 only.

SavethespreadsheetasActivity_8l.

Advice

Ifyouwanttowraptextwithinacellsothatitwrapsataparticularpoint,click

the cursor at that point in the text, then hold down <Alt> and press <Enter>.

Activity 8m

Open the file Gradebook.csv.

Format the top of the

spreadsheet to look like this.

Add appropriate functions to

cellsC3toH6fortheaverage

grades for each subject for each class, rounded as whole numbers. Set the print

area of the spreadsheet to show cells B1 to H6 only.

SavethespreadsheetasActivity_8m.

Mathematical averages

We have already used the AVERAGE function to calculate the mean value

and calculated conditional average calculations using the mean. There are also

functions used to calculate the values for the median and mode.

Task 8v

Open the file Test.csv inaspreadsheet.

Format the spreadsheet to look like this.

Fourteen students have yet to sit the test. Enter

functions into cells C2 to C4 to calculate each

type of average score. Round the mean score

to one decimal place.

SavethespreadsheetasTask_8v.

Open the file and format it as shown in the question using the skills learnt

earlier in the chapter. Enter into cell C2 the formula

=ROUND(AVER AGE($C$8:$C$80),1), which will return the rounded value

5.5. The range has been extended from C66 to C80 as 14 students have yet to

sit the test.

178

8 SPREADSHEETS

MEDIAN function

The

MEDIAN function is used to find the middle value when all data items are

listed in ascending order. Enter in cell C3 the formula

=MEDIAN($C$8:$C$80), which will return the value 6.

MODE function

The

MODE function is used to find the piece of data (in this case

test score) that occurs most frequently. Enter in cell C4 the formula

=MODE($C$8:$C$80), which will return the value 7 as more students scored

7 on the test than any other score.

Nested IF functions

Sometimes nested IF functions are required to display one of several different

possible results, which are dependent on different conditions that have been

tested. It is important for you to work in a logical order. You may have to use

three or more conditions involving numerical values, so work through the

smallest to the largest numerical values, or vice versa. For example, exam results

may be graded fail (less than 40%), pass (40% or more), merit (60% or more), or

distinction (80% or more). A sensible approach would be to deal with 40 first,

60 next and finally 80. It would be extremely difficult to start with 60. Do not

start with middle values; this will give incorrect results.

Task 8w

OpenthefileyousavedasTask_8v. Place formulae in cells D8 to D66 that display:

» Excellent if the test score was 8 or more

» See your tutor if the test score was less than 4

» More revision needed foranyothertestscore.

SavethespreadsheetasTask_8w.

Before starting this task on the spreadsheet, reorder the list so that the

conditions follow a logical pattern, for example lowest to highest like this:

» Score <4 then See your tutor

» Score <8 then More revision needed

» Score 8 or more then Excellent.

Open the file and enter in cell D8 the formula

=IF(C8<4,"See your

tutor",IF(C8<8,"More revision needed","Excellent")).

If the condition is TRUE then the first result is produced ("See your tutor" is

displayed in the cell) but if it is FALSE it moves on to the second result which

is in the form of another IF condition. Be very careful to get the brackets

correct; each condition has one open and one close bracket. When you work

through this formula, it checks whether the value is less than 4 first; if so, it

displays the matching text. Then if it is not true, it checks if the value is less

than 8 next; if so, it displays the matching text. As the only other possible result

is that the value must be greater than or equal to 8, the remaining piece of

text ("Excellent") is displayed. There is, therefore, no need to include another

condition (IF C8>=8, "Excellent"). Replicate this formula into cells D9 to D66.

An alternative method for this would be to use the IFS function studied earlier

in the chapter. Save the spreadsheet as Task_8w.

179

8.1 Create a spreadsheet

Activity 8n

Openthefileyousavedas

Activity_8i.

Editandformatthetopofthe

spreadsheet to look like this.

PlaceformulaeincellsC6toC14thatdisplay:

» Too many days if they worked 23 days or more

» See manager if they worked less than 19 days

» Within tolerance foranyothernumberofdays.

SavethespreadsheetasActivity_8n.

SUBTOTAL function

SUBTOTAL is a function that allows a number

of functions to be used in one cell. The first

variable is a number between 1 and 10, which

tells it which function to use. The remaining part

of the function contains the range of cells this

function is applied to. For example, if the function

=SUBTOTAL(1,A10:A14) is used, it will calculate

the AVERAGE of the range A10 to A14. If this

function was changed to =SUBTOTAL(9,A10:A14),

it would calculate the SUM of this range. There are

many ways of using the SUBTOTAL function, some

of which are shown in Table 8.5.

Simple lookup functions

The term ‘lookup’ means to look up from a list.

There are four functions that can be used for this.

These are:

LOOKUP, HLOOKUP, VLOOKUP

and XLOOKUP. Data may be ‘looked up’ (referenced) from the same

worksheet or workbook or from an external data file. Using external data files is

good practice in industry, but it is important that you do not edit the external

data files (unless instructed to).

LOOKUP function

The list of data that is looked up is contained in a range, often consisting of

two columns or two rows. LOOKUP is used to look up information using data

in the first row or the first column of the range of cells and returns the value

contained in the cell corresponding to the matched value in the first row or

column. This is probably the least useful of the three formulae, especially as

the VLOOKUP, HLOOKUP and XLOOKUP functions offer the user more

flexibility.

HLOOKUP function

HLOOKUP is a function that performs a horizontal lookup of data. This

should be used when the values that you wish to compare your data with are

stored in one row. The values to be looked up are stored in corresponding cells

in the rows below it.

Code Function

1 AVERAGE

2COUNT

3COUNTA

4MAX

5MIN

6PRODUCT

7STDEV

8STDEVP

9SUM

10 VAR

▲ Table 8.5 SUBTOTAL

function codes and their

use

180

8 SPREADSHEETS

Task 8x

OpenandexaminethefileTMCVehicles.csv.Placeformulaeinthe‘Type’

column to return the name of the type of each vehicle.

SavethespreadsheetasTask_8x.

Open the file. If a question asks you to examine the

file, please do so carefully; in this question it is very

important to recognise that the codes in row 1 are not

in alphabetical order.

Enter into cell C6 the formula

=HLOOKUP(B6,$B$1: $H$3,3,0). This formula

looks up and compares the contents of cell B6 with

the contents of each cell from left to right in the top

(horizontal) row of the range B1 to H3. Note that we

have not included cells A1 to A3 and A5 in this range,

as these are row headings and not part of the data to be

looked up. When it finds a match, the ‘,3’ instructs Excel to take the label (or

value) stored in the third row, which is directly under the matched cell. The

final attribute, in this example, the ‘0’ (or ‘False’) instructs Excel to find an

exact match in the top row. This is critical in data which is not sorted into order.

Replicate your formula down to C13. Save the spreadsheet as Task_8x.

Advice

Thealternativeforthisfinalattributeisa‘1’(or‘True’)whichdoesnotgive

correct results in unsorted data, but gives an approximate match rather than

an exact match. This is the default value if the final ‘0’ (or ‘False’) is missing. Try

changingthisattributeinallformulaefromB6toB13inTask_8wandstudythe

results. You will notice that the result for row 10 shows Tractor rather than Truck.

VLOOKUP function

VLOOKUP is a function that performs a vertical lookup of data. This should be

used when the values that you wish to compare your data with are stored in a single

column. The values to be looked up are stored in the columns to the right of these

cells. The lookup data can be stored either in the same file or in a different file.

Task 8y

OpenandexaminethefileOrders.csv. Place formulae in the ‘Customer name’

column to return the name of the customer for each order, using the file

Client.csv.

SavethespreadsheetasTask_8y.

Open and examine the file Client.csv. You will notice that client codes are

vertically listed but are not sorted into order. The file should look like this.

Leave this file open. Open the file Orders.csv, format the column width to

look as shown below and place the cursor in cell C3. Because the data in the

client file is stored in vertical columns, a VLOOKUP is the most appropriate

formula. Enter the formula

=VLOOKUP(B3,Client.csv!$A$2:$B$10,2,FALSE) into this cell. This

181

8.1 Create a spreadsheet

formula will look up and compare the contents of cell B3 with the contents of

each cell in the left (vertical) column of the range A2 to B10 within the file

Client.csv. When entering this formula, you can add the yellow highlighted

section of the formula by moving the cursor into this file and dragging it to

highlight all of the cells in both columns, so it includes the lookup value and the

result. The ‘,2’ in the formula tells Excel to look in the second column of this

range. The ‘False’ (or ‘0’) condition forces an exact

match. When it finds a match, it will take the value or

label stored in the second column which is to the right

of the matched cell. Replicate this formula into cells

C4 to C22. Save the spreadsheet as Task_8y. The

first few results should look similar to this.

Activity 8o

Usingsuitablespreadsheetsoftware,openandexaminethedatainthefiles

AlpHotels.csv and Rooms.csv.

Open the file RoomRates.csv andsavethisasaspreadsheet.Row1contains

the room type code and column A contains the hotel code. Enter formulae in

appropriate cells in row 2 to display the room type for each code.

Enter formulae in the appropriate cells to display, in column:

» Bthenameofeachhotel

» Ctheaddressofeachhotel

» Dthezipcodeforeachhotel.

MergecellsB2toD2.HidecolumnAandrow1.Savethespreadsheetas

Activity_8o.

XLOOKUP function

XLOOKUP is a new function in Excel that can be used to perform either

a horizontal or a vertical lookup of data. This is similar to HLOOKUP and

VLOOKUP but is more powerful and flexible than either of these. It will also

reference data stored in rows/columns before the lookup value. It therefore

allows backward referencing within an array. The values to be looked up can be

stored to either the right or left or above or below the lookup array. The lookup

data can be stored either in the same file or in a different file.

Advice

XLOOKUPandthefollowingTaskcanonlybeattemptedwiththelatestversions

of Excel.Ifyouhaveoldersoftware,skipthissectionandmoveontoAdvanced

lookup functions.

Task 8z

Open the file Tasks.csv. Insert formulae in the CurrentTask column to look up

the client, using the TaskCode for the lookup value and the file Client1.csv. Make

sure that you use both absolute and relative referencing within your function.

Open and examine the file Tasks.csv and click the left mouse button to place

the cursor in cell C3. The Task instructs you to use the file Client1.csv for the

lookup. Open this file in a new spreadsheet. Examine the layout of this file to

decide which type of lookup formula to use. Client1.csv looks like this.

182

8 SPREADSHEETS

At first glance, because it is stored with the lookup data in vertical columns, a VLOOKUP

may appear the correct function, but the TaskCode column is to the right of the Client

column and VLOOKUP cannot do backwards referencing. Therefore, the most

appropriate function to use for this task would be

XLOOKUP. Enter the formula

=XLOOKUP(B3,Client1.csv!$B$2:$B$8,Client1.csv!$A$2:$A$8,"Not found",0,1)

into this cell. This formula will look up and compare the contents of cell B3 with

the contents of each cell in the range B2 to B8 within the file Client1.csv. When

entering this formula, you can add the yellow highlighted section of the formula by

moving the cursor into this file and dragging it to highlight all the cells in this range,

so it includes only the TaskCodes (the lookup array). You can also add the green

highlighted section of the formula by moving the

cursor into this file and dragging it to highlight all

the cells in this range, so it includes only the names

of the clients (the return array). The text “Not

found” is displayed if the TaskCode is not found

during the look up. The number 0 is the match

mode, the 0 tells the function that we want an

exact match only. The final number 1 instructs the

function to search through the items first to last as

the TaskCodes are in ascending order in the source

file. When it finds a match, it will take the value or

label in the return array which is the range A2:A8.

Replicate this formula into cells C4 to C24. Save

the spreadsheet as Task_8z. The first few results

should look similar to this.

Advanced lookup functions

Simple lookup functions enable many solutions but have limitations. You have

to work out whether vertical or horizontal is required and you can only look

down a list (in VLOOKUP) or to the right (in HLOOKUP) or either way

using XLOOKUP. Other more advanced lookup solutions such as

INDEX and

MATCH can work with both horizontal and vertical ranges of data. INDEX and

MATCH together become a nested function, which makes this type of solution

more complex, although it offers greater flexibility. We will first look at each

function individually, then how to combine them for a two-way lookup.

INDEX function

The INDEX function returns values from a given location in a table, where the

user specifies the row and column position of the item in the table.

Task 8aa

Tawara’stopU17athleteshavebeentimedfor

the100msprint.Theresultshavebeenstoredin

Athletics.csv.Openandexaminethisfile,saveitasa

spreadsheet and format it like this.

The sporting directors are choosing the athletes for the 100

m relay teams. They have decided to use the fastest runner

in position 4, then the next fastest in position 1, then the

next fastest in position 2 and the fourth fastest in position

3. The fifth and sixth fastest runners will be selected as

reserves.Enterformulaeintheappropriatecellssothat

the teams (in running order) and reserves are displayed.

SavethespreadsheetasTask_8aa.

183

8.1 Create a spreadsheet

Open and examine the file Athletics.csv; you will notice that lists of athletes

are split into male and female and ordered with the fastest athletes at the top.

Format the spreadsheet as shown.

Enter in cell B6 the formula

=INDEX($A$16:$C$40,2,2), which looks in

the range of cells between A16 and C40, finds the second row in that range,

then returns the value held in the second column, which in this case is the

name of Jade Naylor. Jade was the second fastest runner, so will run first in

the relay race. In cell B7, a similar formula

=INDEX($A$16:$C$40,3,2)

finds the third row in that range and returns the second cell within that row,

which returns ‘Paige Rice’. Copy, paste and edit these formulae for all 12

athletes. The formulae and results should look like this.

Save the spreadsheet as Task_8aa.

MATCH function

The MATCH function searches for a specified item in a range of cells and

returns the relative position of that item in the range.

Task 8ab

Open and examine the file SkiRace.csv.

Skiersfinishthecourseasfastaspossible

butgodownthecourseoneatatime.

Openandexaminethisfile,saveitasa

spreadsheet and format it like this.

Without changing the data, display the

data for the skier who had:

» astartingpositionofnumber64

» finished first.

SavethespreadsheetasTask_8ab.

Open and examine the data in the file. The reference position in the list of

skiers needs to be located, as the order of the list will not change. Enter the test

data 30 into cell B2, as this is the first item in the list to be referenced. Enter in

cell B3 the formula

=MATCH($B$2,$B$10:$B$165,0), which matches the

contents of cell B2 (in this case the test data 30) with the first 30 it finds within

the range B10 to B165. The final ‘,0’ instructs Excel to find an exact match.

This test data gives a list reference of 1 as this is the first position in the list.

Change cell B2 to 77, which returns a list reference of 2.

184

8 SPREADSHEETS

Place in cell E3 the formula =MATCH($E$2,$E$10:$E$165,0) and 111 as

test data in cell E2, which will return a value of 1 (the first item in the list).

Advice

Thealternativesforthisfinalmatch-typeattributeisa1forLess than,ifanexact

match cannot be made, or -1 for Greater than.TrychangingthetestdatainB2to

30.6 where an exact match cannot be found, then change this attribute to 1, then

-1 and study the results. You will notice that if the data is not in ascending order

withthe1attributeitgiveserrors,andifthedataisnotindescendingorderwith

the -1 attribute it gives errors.

INDEX and MATCH together

When INDEX and MATCH are used together, they allow us to look up a value

in a table using both rows and columns. We will study that later, but first let us

get used to using the functions together.

In a copy of Task 8ab, enter in cell B4 the formula

=INDEX($A$10:$E$165,

MATCH($B$2,$B$10:$B$165,0),1)

, which nests a MATCH function within

the INDEX function. The formula looks within the range A10 to E165, using

the row position (list reference derived using the MATCH function, highlighted

here in yellow) and the column position set to the value 1. The yellow portion is

the function placed in cell B3 to work out the row position within the data.

Advice

ForthisTaskwecouldhaveplacedtheformula=INDEX($A$10:$E$165,B3,1) in B4,

whichwouldgivethesameresult;howeverthisisoftenlessefficientasituses

two cells to perform the task rather than one.

Place similar formulae in cells B5 to B7 and in E4 to E7 so that they look like this:

Now change B2 to contain 64 and E2 to

contain 1. Save the spreadsheet as Task_8ab.

Thespreadsheet should look like this.

Task 8ac

OpenandexaminethefileCityTemp.csv. This shows the average temperature in

some American towns and cities for each month.

Use this to create a spreadsheet so that the name of the city and the month are

entered into cells B3 and B4 and the average temperature for that time and place

are returned in cell B5.

Use this to display the temperature in Denver in February.

SavethespreadsheetasTask_8ac.

185

8.1 Create a spreadsheet

Open and examine the data in the file. Place test data ‘Akron’ (which is a short

name so is quick to type and near the top) in cell B3 and ‘Jan’ in B4. Enter in

cell B5 the formula

=INDEX(B8:M253,MATCH(B3,A8:A253,0),

MATCH(B4,B7:M7,0)), which nests two MATCH functions within the

INDEX function. It looks within the range B8 to M253, using the row position

(list reference) highlighted in yellow and the column position highlighted

in green. The row position (yellow) matches the contents of cell B3 with

the names of the cities in the range A8 to A253 and the ‘,0’ forces an exact

match. The column position (green) matches the contents of cell B4 with the

abbreviated names of each month in the range B7 to M7; again the ‘,0’ forces

an exact match. The value returned for the test data should be -3.8 degrees.

It is important to get the order of this correct with the row positions before

the column positions. Change the data so that B3 contains ‘Denver’ and B4

contains ‘Feb’. Save the spreadsheet as Task_8ac.

Activity 8p

Open the file TGCosts.csv andsavethisasa

spreadsheet. Format the spreadsheet to look like this.

Twolookuptableshavebeenprovided,somakesure

you select the correct table for this activity. The length

and width of the required granite are entered into cells

B4andB5bytheuser.Thepricewillbedisplayedin

cell B6. If a customer wants granite that is smaller

thanoneofthestocksizes,itwillbecutfromthe

nearestsizeandthepriceforthatsizewillbecharged.

Usethespreadsheettocalculatethepriceofgranitewithalengthof262andawidthof148.Savethe

spreadsheet as Activity_8p.

Working with strings

Concatenate strings

A string is a name given to a list of text characters, but it can include numbers.

The word concatenate means to join (link together) and there is both a function

and an operator in Excel to perform this.

Task 8ad

OpenandexaminethefileEmployees.csv.

Place a formula in cell:

» H2 to display the employee’s full name

» I2todisplaytheemployee’snameintheformat‘Family_name:Given_name’.

Place a function in cell:

» J2 to display the employee’s full name

» K2todisplaytheemployee’snameintheformat‘Family_name:Given_name’

» L2todisplaytheentireaddresswiththepostcode,witheachpartseparated

withacommathenaspace.

Replicate these for all employees. Save the spreadsheet as Task_8ad.

The easiest method of joining two string values is to use the & (ampersand)

operator. Open and examine the file Employees.csv. Enter in cell H2 the

186

8 SPREADSHEETS

formula =B2&" "&C2, which takes the contents of cell B2, adds a space, then

the contents of cell C2, so that it displays ‘Kristy King’.

Enter in cell I2 the formula

=C2&": "&B2, which takes the contents of cell C2,

adds the text in the speech marks (a colon and a space), then the contents of cell

B2 so that it displays ‘King: Kristy’.

This method cannot be used to answer the final three questions as the question

explicitly asks us to ‘Place a function in …’ and the ampersand operator is not a

function, because functions have names.

CONCATENATE function

The

CONCATENATE function joins up to 30 string items

together and displays the result as text. Enter in cell J2 the formula

=CONCATENATE(B2," ",C2), which takes the contents of cell B2, adds a

space, then the contents of cell C2 so that it displays ‘Kristy King’. Compare

this function and its result with that placed in cell H2.

Enter in cell K2 the formula

=CONCATENATE(C2,": ",B2), which takes the

contents of cell C2, adds the text in the speech marks (a colon and a space), then

the contents of cell B2 so that it displays ‘King: Kristy’. Compare this function

and its result with that placed in cell I2.

Enter in cell L2 the formula

=CONCATENATE(D2,", ",E2,", ",F2), which

returns the address for this employee stored as a single string, with comma

delimiters so that the data can be extracted from it again if required.

Replicate these formulae for all employees. Save the spreadsheet as Task_8ad.

Extracting data from strings

Data can be extracted from strings in many different ways. There are three

functions that are most often used for this process. They extract characters

from the left, right or middle of the string. As well as extracting data, you can

identify the length of the string, and there are other functions that allow you to

find the position of a character (or substring) in a string.

Task 8ae

OpenandexaminethefileEmployees2.csv.

Place formulae to extract from the employee reference number, in cell:

» D2todisplaythefirstthreecharacters

» E2 to display the last five characters

» F2todisplaythecodefortheworkplacewhichisstoredasthethird

character

» G2todisplaythethirdtotheseventhcharacters(inclusive).

Place a formula in cell:

» H2todisplaythelengthoftheemployee’sname

» I2tofindthepositionofthecoloninthename

» J2 to display the employee’s family name

» K2todisplaytheemployee’sgivenname

» M2todisplaythenameofthetownorcitywheretheemployeelives.

Replicate these for all employees. Save the spreadsheet as Task_8ae.

LEFT function

The

LEFT function extracts a number of characters from the left-hand side of a

string.

187

8.1 Create a spreadsheet

Open and examine the file Employees2.csv. Enter in cell D2 the formula

=LEFT(A2,3), which displays the first three characters from the contents of cell

A2, so that it displays ‘CTD’.

RIGHT function

The

RIGHT function extracts a number of characters from the right-hand side

of a string.

Enter in cell E2 the formula

=RIGHT(A2,5), which displays the last five

characters from the contents of cell A2, so that it displays ‘0001D’.

MID function

The MID function extracts a number of characters from the middle of a string.

To use the MID function, you must specify the string to be extracted from,

then the start position within the string, then the number of characters to be

extracted.

Enter in cell F2 the formula

=MID(A2,3,1), which extracts from the contents

of cell A2, starts at the third character, and extracts just a single character, so

that it displays the letter ‘D’.

Enter in cell G2 the formula

=MID(A2,3,5), which performs a similar extract

but takes five characters rather than one (as the third to seventh character

requires five), so that it displays the string ‘D0001’.

LEN function

The

LEN function counts the number of characters within a string. This

function is very easy to use and is useful when included in nested functions to

solve more complex tasks.

Enter in cell H2 the formula

=LEN(B2), which counts all the characters held in

B2 including the colon and the space and returns the value 12.

Searching within a string

There are two functions in Excel that allow you to search within a string and

both work in a similar way. The

FIND function allows case-sensitive searching

but cannot be used with wildcard characters. The SEARCH function is case-

insensitive but allows searching with a * (wildcard symbol). Both would provide

similar solutions to these tasks but for these exercises we will only study the

FIND.

FIND function

The FIND function returns a numeric value that represents the position of a

character or substring within a string.

Enter in cell I2 the formula

=FIND(":",B2), which looks character by character

for the string ":", in this case a single colon. When it finds the first colon in the

string held in B2, it returns the numeric position of that character, which in this

case is 5.

This will be needed to allow us to split the name into two parts, as most

names are not the same length. The family name is stored as the first part

of the name in cell B2 so we will use the LEFT function for the extraction.

Enter in cellJ2 the formula

=LEFT(B2,FIND(":",B2)-1), which takes the

left characters from B2. To find the number of characters to be extracted,

Excel has located the position of the colon in the string (as we did in cell I2)

188

8 SPREADSHEETS

and has extracted all characters up to that point. The -1 removes the colon

itself from the extract. This returns the string ‘King’.

The given name is stored as the second part of the name in cell B2, so we will

use the RIGHT function for the extraction. This is slightly more complex,

because we have to have to work out the length of the overall string and

subtract the number of characters up to the colon, as well as the space, to

get the number of characters in the given name. Enter in cell K2 the formula

=RIGHT(B2,(LEN(B2)-FIND(":",B2)-1)), which takes the right characters

from B2. To find the number of characters to be extracted, Excel has calculated

the length of the string, then subtracted the length of the family name and

the-1 removes the space. This returns the string ‘Kristy’.

The extraction of the name of the town or city from the address requires two

stages. As FIND (and SEARCH, had we chosen to use that) finds the position

of the first character (or substring) searching from left to right, we must find the

first comma in the address, extract the text after it (not including the space) and

then remove the text including and after the second comma. It is not necessary

to use two cells for this but it will be easier for you to understand the method.

Enter in cell L2 the formula

=MID(C2,FIND(",",C2)+2,50), which although

it uses a MID function, extracts the right characters from C2 without having to

calculate its length. The length of each address, each town and each postcode

may be different. We cannot work out the exact number of characters to extract

as there are two commas in the address string. A figure that is too large has

been chosen for the number of characters, in this case 50, to cater for any long

addresses that may be added in the future. This formula returns ‘Dundee, DU9

3UH’.

Enter in cell M2 the formula

=LEFT(L2,FIND(",",L2)-1), which extracts

the left characters up to (but not including because of the -1) the comma and

returns ‘Dundee’.

These two functions could have been entered as a single function, but the

efficiency of this is debatable as the content of the calculation in L2 is used

twice within M2. Enter into cell N2 the formula

=LEFT(MID(C2,FIND

(",",C2)+2,50),F I ND(",",MID(C2,FIND(",",C2)+2,50) )-1), which is the

combined result. Which do you think is the most efficient method?

Replicate these formulae for all employees. Save the spreadsheet as Task_8ae.

Testing cell contents

There are three Excel functions that can be used to test whether a cell contains

text, numbers or non-text items. Each of these functions returns either ‘True’ or

‘False’ and can be used in string manipulation. For these three examples we will

check the contents of cell A5 in a new spreadsheet. After entering the formula,

change the contents of cell A5 to see the results.

ISTEXT function

This function, for example

=ISTEXT(A5), checks to see if a cell contains text

(including special characters).

ISNUMBER function

This function, for example

=ISNUMBER(A5), checks to see if a cell contains

numeric data.

189

8.1 Create a spreadsheet

ISNONTEXT function

This function, for example

=ISNONTEXT(A5), checks to see if a cell is blank

(has no contents).

Activity 8q

Open the file Employees3.csv,examineitcarefullyandsavethisasa

spreadsheet.

EnterformulaeincolumnsD,EandFtodisplayeachemployee’saddress

separatedintoitsthreeparts.

SavethespreadsheetasActivity_8q.

Changing case

Strings can be set into upper case (capitals), lower case or a mixture of the two.

We are now going to create a mixture of upper-case and lower-case text by

forcing text to be upper and lower case and then concatenating the parts.

Task 8af

OpenandexaminethefileStrings.csv.

Enter a function in cell:

» B5 to display the original string in capitals

» B6 to display the original string in lower-case text

» B7andB8tocomparestringAandstringB

» B9andB10toreturntheASCIIvaluesofstringsAandB

» B12toreturnthecharacterfromthegivenASCIIvalue

» B13toreturnthelengthoftheoriginalstring

» B16andB17toreturnthevaluesinbinaryandhexadecimal

» B20andB21toreturnthevaluesindecimalandhexadecimal

» B24andB25toreturnthevaluesinbinaryanddecimal.

SavethespreadsheetasTask_8af.

UPPER function

The UPPER function returns the contents of a string as upper-case characters.

Open the file and place the cursor in cell B5. Enter the formula =UPPER(B1),

which will return the contents of cell B1 set into upper case.

LOWER function

The

LOWER function returns the contents of a string as lower-case characters.

Open the file and place the cursor in cell B6. Enter the formula

=LOWER(B1),

which will return the contents of cell B1 set into lower case.

Comparing strings

Excel can compare strings in many ways. Most of these methods ignore case. For

example, enter in cell B7 the formula

IF(B2=B3,"Y","N"), which will compare

the two cells and return ‘Y’ if they are the same and ‘N’ if they are not the

same. In this case the result is ‘Y’ which tells us that Excel does not compare

case with an IF function. It also ignores case with other functions including

VLOOKUP, HLOOKUP and INDEX and MATCH.

190

8 SPREADSHEETS

EXACT function

The

EXACT function compares two strings to check that they are exactly the

same (including case).

Enter in cell B8 the formula

=IF(EX ACT(B2,B3),"Y","N"), which returns the

expected result of ‘N’, as the two strings are not identical.

Convert strings to ASCII values

It may be necessary to convert a string into an ASCII value, which is the code

used by a computer to identify each letter or symbol on the keyboard.

CODE function

The

CODE function returns the ASCII (numeric) code for a given character. If

more than one character is present in a string, this function returns the code of

the first character.

Enter in cell B9 the formula

=CODE(B2), which returns the value 65. Enter in

cell B10 the formula =CODE(B3), which returns the value 97. We can see that

the ASCII codes for these two strings are very different.

CHAR function

The

CHAR function returns the ASCII character for a given numeric code.

Enter in cell B12 the formula

=CHAR(B4), which returns the ASCII character

for this numeric code, in this case the character ‘q’.

Enter in cell B13 the formula =LEN(B1), which returns the value 11, as there

are five letters + one space + five numbers.

Working with number bases

We are familiar with using numeric values in our spreadsheet; these are all

displayed in base 10 (decimal), but sometimes we need to convert numbers

between different number bases, often to/from base 2 (binary) or base 16

(hexadecimal). In Excel there are some functions that convert these values.

DEC2BIN function

The DEC2BIN function converts a decimal (base 10) number into its binary

(base 2) equivalent.

Enter in cell B16 the formula

=DEC2BIN(B15), which returns, for the decimal

number 78, the value 1001110.

DEC2HEX function

The DEC2HEX function converts a decimal (base 10) number into its

hexadecimal (base 16) equivalent.

Enter in cell B17 the formula

=DEC2HEX(B15), which returns, for the

decimal number 78, the value 4E.

191

8.1 Create a spreadsheet

BIN2DEC function

The BIN2DEC function converts a binary (base 2) number into its decimal

(base 10) equivalent.

Enter in cell B20 the formula

=BIN2DEC(B19), which returns, for the binary

number 11111111, the value 255.

BIN2HEX function

The BIN2HEX function converts a binary (base 2) number into its hexadecimal

(base 16) equivalent.

Enter in cell B21 the formula

=BIN2HEX(B19), which returns, for the binary

number 11111111, the value FF.

HEX2BIN function

The HEX2BIN function converts a hexadecimal (base 16) number into its

binary (base 2) equivalent.

Enter in cell B24 the formula

=HEX2BIN(B23), which returns, for the

hexadecimal number FE, the value 11111110.

HEX2DEC function

The HEX2DEC function converts a hexadecimal (base 16) number into its

decimal (base 10) equivalent.

Enter in cell B25 the formula

=HEX2DEC(B23), which returns, for the

hexadecimal number FE, the value 254. Save the spreadsheet as Task_8af.

Date and time functions

Although date and time values in Excel are displayed as dates/times, each one is

actually stored as a number. The integer part of the number stores the date and the

decimal part of the number stores the time. The number 1 represents 1 January

1990, the number 2 represents 2

January 1990 and so on. Times are stored as parts

of the day, 0.5 is 12 noon, so a value of 2.5 would be 12 noon on 2 January 1900.

1 January 2022 at 6 a.m. would be stored as 44 562.25 because this is 44 562 days

after 1 January 1900 and 6 a.m. is exactly a quarter of the way through the day.

Advice

Datesshowninthischapterareinday,month,year(UK)format.Ifyouusemonth,

day, year (US) format you will need to adjust your formulae accordingly.

WEEKDAY function

WEEKDAY is used to return a number between 1 and 7 from a given date. If

the day is a Sunday, 1 is returned, Monday is 2, and so on.

Task 8ag

Open and examine the file JanSales.csv.Foreachorder,displayincolumnFthe

day of the week that the order was placed. Save the spreadsheet as Task_8ag.

Open and examine the data in the file. Enter in cell E2 the formula

=WEEKDAY(C2), which returns the code for the day, in this case ‘2’. Enter

in cell F2 the formula =VLOOKUP(E2,$H$2:$I$8,2,0), which looks up the

code and displays the text from the adjacent cell, in this case ‘Monday’.

Replicate both formulae for all orders. Save the spreadsheet as Task_8ag.

192

8 SPREADSHEETS

Task 8ah

Createanewspreadsheetlikethis.

Place functions in cells B2, B3 and B4 to display

the day, month and year parts of the date in cell B1.

SavethespreadsheetasTask_8ah.

DAY function

DAY is a function that is used to return the day part of a given date, as a

number between 1 and 31.

Create a new spreadsheet as shown and enter in cell B2 the formula =DAY(B1),

which will return the value 20.

MONTH function

MONTH is a function that is used to return the month part of a given date, as

a number between 1 and 12.

Enter in cell B3 the formula

=MONTH(B1), which will return the value 5.

YEAR function

YEAR is a function that is used to return the year part of a given date.

Enter in cell B4 the formula =Y EA R(B1), which will return the value 2022.

Save the spreadsheet as Task_8ah.

Task 8ai

Createanewspreadsheetlikethis.

Place functions in cells B5, B6 and B7 to

display the number of days, months and

years, respectively, between the first date

and the second date.

SavethespreadsheetasTask_8ai.

Calculate the number of days between two dates – Method 1

Create a new spreadsheet as shown. To calculate the number of days between

two dates, do not use any date functions, just subtract the first date from the

second by entering in cell B5 the formula

=B2-B1, which will return the result

of the calculation in date format as 05/07/1901. To display the number of days,

you need to format this cell as a number. Select the Home tab, in the Number

group click on the drop-down menu for formatting like this.

Select the top option for

General formatting and the value shown in the cell

will change to 552.

Try swapping the two dates entered so that the first date is 23/11/2023 and

the second date is 20/05/2022. This will result in a negative value in cell B5.

To avoid negative values, you can use the ABS function.

193

8.1 Create a spreadsheet

Calculate the number of years between two dates – Method 1

At first glance it would appear that to calculate the number of years between

two dates, you need to subtract the YEAR function of the first date from the

YEAR function for the second date. This does find the difference in years

between two dates, but if the two dates were 31/12/2022 and 1/1/2023 then

this would return a one-year difference even though there is only really a one-

day difference. It is more accurate to use the number of days and divide this by

365.25 (which is the approximate number of days per year).

Enter in cell B7 the formula

=INT((B2-B1)/365.25), which will return the

value 1.

DATEDIF function

Although undocumented in Excel, the DATEDIF function will calculate the

date difference between two dates and display the number of days, months or

years between the two dates. The syntax for this function is

=DATEDIF(start

date, end date, return code).

The start date and end date MUST be in the correct order and the different

return codes can be seen in Table 8.6.

Code What it does

"D" Returns the difference in days between the start date and end date.

"M" Returns the difference in months between the start date and end date.

"Y" Returns the difference in years between the start date and end date.

"MD" Returns the difference in days, ignoring the months and years, between the

start date and end date.

"YM" Returns the difference in months, ignoring the days and years, between the

start date and end date.

▲ Table 8.6 Ways of using the DATEDIF codes

Calculate the number of days between two dates – Method 2

Enter in cell B12 the formula

=DATEDIF(B$1,B$2,"D"), which will return

the value 552.

Calculate the number of months between two dates

Enter in cell B13 the formula

=DATEDIF(B$1,B$2,"M"), which will return

the value 18.

Calculate the number of years between two dates – Method 2

Enter in cell B14 the formula

=DATEDIF(B$1,B$2,"Y"), which will return

the value 1.

Save the spreadsheet as Task_8ai.

Task 8aj

Createanewspreadsheetlikethis.

Place functions in cells B3, B4 and B5 to

display the hour, minute and second parts

of the time in cell B1.

SavethespreadsheetasTask_8aj.

194

8 SPREADSHEETS

HOUR function

The

HOUR function is used to return a number between 0 and 23 from a

given time.

Create a new spreadsheet as shown and enter in cell B3 the formula

=HOUR(B1), which will return the value 18, because Excel stores the time

using the 24-hour clock.

MINUTE function

MINUTE is a function that is used to return a number between 0 and 59 from

a given time.

Enter in cell B4 the formula

=MINUTE(B1), which will return the value 7.

SECOND function

SECOND is a function that is used to return a number between 0 and 59 from

a given time.

Enter in cell B5 the formula

=SECOND(B1), which will return the value 3.

Save the spreadsheet as Task_8aj.

Task 8ak

Create a new spreadsheet like

this.

Place functions in cells B5, B6

and B7 to display the difference

between the two times in terms

of the number of:

» hours

» hours and minutes

» hours,minutesandseconds

between the first time and the second time.

SavethespreadsheetasTask_8ak.

The same calculation will be performed for all three of these different results,

by subtracting one time from the other (and if required, using the ABS function

to ensure a positive result). The format of the resulting answer can be specified

using the TEXT function, depending whether the result is required in hours,

hours and minutes, or hours, minutes and seconds.

Enter into cell B5 the formula

=TEX T(A BS($B$2-$B$1),"h"), which will find

the difference between the two cells, make sure that it is a positive difference

and then format the cell as text displaying only the hour portion. This displays a

2 in this cell.

Use the drag handle to replicate this formula into cells B6 and B7. Edit the

formula in cell B6 to

=TEX T(A BS($B$2-$B$1),"hh:m m"), which formats the

result to hours and minutes. The "hh" forces the hours to display as two digits

like this ‘02:53’.

Edit the formula in cell B7 to

=TEX T(A BS($B$2-$B$1),"hh:m m:ss"), which

formats the result as hours, minutes and seconds and displays ‘02:53:00’. Save

the spreadsheet as Task_8ak.

195

8.1 Create a spreadsheet

Task 8al

OpenandexaminethefileBusTimes.csv.

Foreachjourney,ifthebuswaslate,calculatethenumberofminutesitwaslate.

Calculate the total number of minutes that the buses were late.

Save the spreadsheet as Task_8al.

This problem is not as simple as it appears. One solution would be to check

if the bus was late, then to calculate how late it was. As the data is formatted

and displayed as text, it needs converting into a numeric value so that the total

minutes late can be calculated. This solution assumes that no bus is going to be

more than a few hours late.

Open and examine the file. Enter in cell E2 the formula

=IF(D2>C2,TEXT(D2-C2,"hh:mm"),"00:00"), which displays ‘00:00’ if the

bus was on time or early and displays the time a bus was late as a text string in

"hh:mm" format if the bus was late.

Enter in cell F2 the formula

=(LEFT(E2,2)*60+R IGHT(E2,2)), which

multiplies the left two characters of E2 (the hour part of the late column) by 60

to turn them into minutes. This is added to the two right characters of E2 (the

minutes part) to give the total minutes late stored as an integer value. This only

works on the data stored in cell E2 because it is stored as text. It will not work

on the data in cells such as C2 and D2, which are not stored as text.

Replicate these formulae for all journeys. Enter in cell F119 the formula

=SUM(F2:F118), which returns a value of 467. Save the spreadsheet as

Task_8al.

Task 8am

Createanewspreadsheetlikethis.

Make sure that all cells are appropriately

formatted as date and time. Calculate

the flight duration in hours and minutes.

SavethespreadsheetasTask_8am.

As long as cells B2 and B5 are formatted as date and cells B3 and B6 are

formatted as time, the following solution works.

Enter in cell B8 the formula

=TEXT((B5+B6)-(B2+B3),"hh:mm"), which

displays 14:55 in the correct format.

Save the spreadsheet as Task_8am.

Activity 8r

Open the file Flights.csv andsavethisasaspreadsheet.Foreachflight,

calculate its duration and display this in hours and minutes. Calculate the

average duration for all flights.

SavethespreadsheetasActivity_8r.

196

8 SPREADSHEETS

Visually verify the accuracy of your data entry

The most common reason for making mistakes is the lack of accurate data

entry. Although it appears obvious, each label and item of data entered should

be carefully checked to make sure that there are no spelling errors or errors in

the spacing or case (letters with upper or lower case) of the labels entered. You

should visually verify this by comparing the label or data with that provided in

the task.

8.1.3 Validation rules

Data entry errors can also be reduced by adding validation rules to your

spreadsheet.

Task 8an

OpenandexaminethespreadsheetyousavedasTask_8v.

Fourteen students have yet to sit the test. All test scores must be whole

numbers. The highest score is 10. Apply appropriate rules to ensure only

acceptable data can be entered.

SavethespreadsheetasTask_8an.

Open and examine the spreadsheet. The task requires you to validate the data

entry, although the word is deliberately not used. Fourteen students have yet

to sit the test, so highlight all cells in the range C8 to C80, which is where the

data has been and will be entered. Select the

Data tab, in the Data Tools group,

click on the

Data Validation icon.

This opens the Data Validation window. In the Allow section, click on the

top drop-down menu arrow.

Select

Whole number from the list. The validation window changes and

the Data group becomes available. Click on the drop-down list and select

between (if it is not already selected). Place 0 in the Minimum value box,

which is the lowest possible test score, and in the Maximum value box,

place10.

Although we have now set the validation rule, we must also enter

appropriate text that will be displayed if a user enters data that does not

meet the rule. Select the

Error Alert tab.

Enter in the

Title box the text Data entry error! to inform the user

that an error has occurred. Enter in the

Error message box the text

‘Please enter a whole number between 0 and 10 (inclusive)’. These

messages clearly inform the user that they have made a mistake and

more importantly what they should do to rectify this error.

It is also useful to display an input message before the user

enters data, telling them what data is acceptable. To do this

select the

Input Message tab.

197

8.1 Create a spreadsheet

Enter an appropriate Title which informs the user what data is being

entered. Add an appropriate Input message that informs the user what

data entry is accepted before they make a data entry error.

Click on the

button to activate the validation rule. We will test

this rule in Section 8.2. Save the spreadsheet as Task_8an.

Task 8ao

OpenandexaminethespreadsheetyousavedasTask_8f.

Newproductsneedtobeaddedtothelist.Theonlychipsetmanufacturers

used by this company are AMD and Intel. A list of supported socket types

can be found in the file Socket.csv. Apply appropriate rules to ensure only

acceptable data can be entered.

SavethespreadsheetasTask_8ao.

Open and examine the spreadsheet. The task requires you

to validate the data entry for the chipset manufacturers in

column G, although not the top four cells. Highlight a

range of cells from G5 to (at least) G130. Open the

Data

Validation window and select the Settings tab. In the

Allow group, select List from the drop-down menu. In

the

Source box type ‘AMD,Intel’ like this.

Add appropriate input and error messages before clicking

. For the supported socket types, open and

examine the file Socket.csv. It contains a list of the

supported socket types with a header row. Copy cells A1

to A12 and paste these as cells into a new worksheet in

your

Task_8f workbook. Rename this worksheet Socket.

Select the Task_8f worksheet and highlight a range of

cells from F5 to (at least) F130. Open the Data Validation

window and select the Settings tab. In the Allow group,

select List from the drop-down menu. Click the left

mouse button in the

Source box and drag the cursor in

the worksheet Socket to highlight cells in the range A2 to

A12 like this.

Add appropriate input and error messages before clicking

. We will test this rule in Section 8.2. Save the

spreadsheet as Task_8ao.

Advice

Youcannotuseexternalreferenceswithdatavalidation

in Excel. Copy the data from the external source into your

current worksheet or workbook.

Task 8ap

OpenandexaminethespreadsheetyousavedasTask_8ac.

Ensure that only valid data can be added in cells B3 and B4.

SavethespreadsheetasTask_8ap.

198

8 SPREADSHEETS

Open and examine the spreadsheet. The formula placed in

cell B5 will generate a #N/A error if invalid data is entered

into B3 or B4. Place the cursor in cell B3. Open the

Data

Validation window and select the Settings tab. In the

Allow group, select List from the drop-down menu. Click

the left mouse button in the

Source box and highlight

cells A8 to A253 like this.

Perform similar action to set validation in cell B4 using the

cell range B7 to M7 as the data source.

As you move the cursor into each of these cells, a drop-

down list of valid cities/months appears to the right of the

cell. This will assist the user as they can now click on the

item instead of typing, which will reduce typographical

errors.

Save the spreadsheet as Task_8ap.

Advice

SelectingfromalistwithdatavalidationinExcel offers the user a drop-down list

of valid options to click on.

Activity 8s

OpenthefileyousavedasActivity_8m.

Apply appropriate validation to ensure that all grades entered are within the

range 1 to 9 inclusive.

SavethespreadsheetasActivity_8s.

8.1.4 Formatting

Format cells

As we have already seen when using date and time functions, the format Excel

stores data in is not the same as the format used to display that data.

Task 8aq

OpenandexaminethefileFormat.csv.

Format

ells

ange

atch

isplay

tyle

escribed

column A.

SavethespreadsheetasTask_8aq.

The quickest way to format cells is to

highlight cells B3 to D3, select the

Home

tab and in the Number group, use the

drop-down menu to select the cells as

General number format.

Repeat this for each display format. Your results may differ

from this, because the regional settings and installation

settings of Excel are likely to be different. Although this is

the quickest method, it does not give you the flexibility to

change the settings like the number of decimal places displayed.

199

8.1 Create a spreadsheet

Format numbers

Highlight cells in the range B4 to D4. Select the

Home tab, in the Number

group click on the dialogue box launcher.

This opens the

Format Cells window. Use this to

change the number of decimal places of this number

from 2 to 1.

You will notice that the numbers displayed have

changed:

From 42.00 0.25 2.5

To 42.0 0.3 2.5

The numbers stored have not changed; for example

if you enter in cell F4 the formula

=2*C4, it will

return the value 0.5 rather than 0.6.

As you can see, the figure displayed in C3 is not the

one used for the calculation. It is important for you

to use functions like ROUND and INT, rather than

just formatting where appropriate.

Format currency

Examine the formatting of cells B5 to D5. The

computer has assumed that because my machine is

configured for the UK, the settings required are £

sterling. Change these three cells into Australian

dollars. To do this select these three cells, then open

the

Format Cells window. Use the drop-down

menu in the

Symbol box to select the required

currency, in this case $ English (Australia).

Some currencies like Japanese yen have no decimal

places and so will need to be formatted to zero

decimal places. If the currency symbol that you

are looking for (for example ¥, the symbol for the

Japanese yen) does not appear in the drop-down list,

use the ISO code for the country, in this case JPY.

These are found at the bottom of the menu list.

200

8 SPREADSHEETS

Format dates

Excel holds a number of preformatted styles for dates.

These are selected by highlighting and opening the

Format Cells window. Use the drop-down menu in the

Type box to select the required date format.

You may find that your list is different to this. These differences

are due to the settings applied to your software, both in

Windows and in Excel. Different regional settings will offer

different format options, for example in the United Kingdom

the date for 25 March 2023 may be formatted as 25/03/2023,

yet on a machine in the USA it may be formatted as

03/25/2023. The regional variations of these settings can be

changed using the drop-down list for Locale (location):.

This allows dates in long-date format (those with a text

component), like 25 March 2023, to be formatted into different languages, such

as 25 de marzo de 2023 in Spanish. You need to know how to produce different

versions of a spreadsheet, formatted for audiences in different parts of the world.

Remember earlier in the chapter, we also applied some specialised date formats

to cells using the text function. This can be used to specify formats that are not

available in these drop-down lists. Enter in cell F6 the formula

=TEXT(B6,"dd mm yyyy"), in G6 =TEXT(B6,"dd mmm yyyy"), in H6

=TEXT(B6,"dd mmmm yyyy"). What do you notice about the results?

Format times

Time values can be formatted in a similar way to the date. Different format

types can be selected from a list of

Type: options. Again, you can restrict the

types to different regional variations using the Locale (location): list.

Format percentages

This is again set in the the

Format Cells window. Highlight cells B9 to D9 and

examine the formatting. These cells have automatically been formatted to two

decimal places, which is not appropriate for the figures displayed. Change it to

display integers by setting the

Decimal Places box to 0.

It is important to remember that if you want to display 25% in a cell, you must

enter 0.25 before formatting the cell as a percentage value. Before working

with percentages, make sure that you understand the maths needed to change

decimals to percentages.

Advice

A shortcut to format cells as percentage values is to use the %

icon in the

Number group of the Home tab.

Format fractions

Examine the cells B10 to D10. You can see that they have been

formatted as fractions. This feature can display simple fractions

but is limited. You can change the number of digits for the

parts of the fraction and the type of fraction required, including

equivalent fractions when using simple fractions like quarters

and eighths. Open the

Format Cells window and change the

formatting in C9 to display the fraction in eighths like this.

Save the spreadsheet as Task_8aq.

201

8.1 Create a spreadsheet

Cell alignment

In the Home tab, locate the Alignment group. This group contains icons to

format the alignment of labels and data within a cell. The first three icons along

the top row are to set the vertical alignment to the top, middle or bottom:

Vertical alignment

Horizontal alignment

Top

Left

Middle

Centre

Bottom

Right

The fourth icon on the top row changes the text direction and we will study

that in the next section of this chapter. The first three icons along the bottom

row are to set the horizontal alignment to the left, centre or right.

Text orientation

It is sometimes difficult to get spreadsheets to appear as we want, especially

when trying to fit them to a single page or screen. Sometimes it is because the

labels along the top of columns are too long; at other times you want a merged

label to the left of the data, but this does not fit well on the page or screen. One

solution is to rotate the text in those cells by ninety degrees so that it is vertical

within the cell rather than horizontal.

Task 8ar

OpenandexaminethespreadsheetyousavedasTask_8o.

InsertanewcolumntotheleftofAandformatittolook

like this.

SavethespreadsheetasTask_8ar.

Open the spreadsheet and insert a new column A. Select cells A1 to E1.

Locate the

Home tab, in the Alignment group click on the Merge & Center

button. This will remove the existing merge and a second click will merge

the cells to include the new column. Insert a new row 3, making this the

same height as row 4. Move the label Type of room into C3, then merge

cells C3 to E3. Move the label Hotel name from B5 to A6 and delete row 5.

Merge cells A5 to A18. Place the cursor in this cell, click on the

Home tab and in

the Number group, click on the dialogue box launcher. This opens the Format

Cells window in the Alignment tab.

202

8 SPREADSHEETS

Locate the Orientation box, grab the red drag handle and

rotate it to a vertical position like this.

Format this cell so that it is

centre-aligned horizontally and

vertically. We do this by staying

in the

Format Cells window

and setting the Horizontal and

Vertical boxes to centre aligned.

An alternative method would be

to use the alignment icons in the

Home tab, Alignment group on

the toolbar.

Save the spreadsheet as Task_8ar.

Format cell emphasis

Font size

When you want to change the font size within a spreadsheet,

you must select the cell or cells to be changed. The font size of the text

within a cell is changed from the Home tab, within the Font group.

You can either use the drop-down menu to select the font size

or type a new value into the box.

Font style

When you want to change the font style within a spreadsheet, you must select

the cell or cells to be changed. The font style (typeface) of the text within a cell

is changed from the

Home tab, within the Font group. To select the font style

that you require, use the drop-down menu.

More options for the font style are available if we click

on the dialogue box launcher to open the Format Cells

window in the Font tab. Enhancements such as bold

and italic are available from the Font style menu,

single or double underline is available from the

Underline drop-down menu,

other effects such as superscript and subscript

are also available here.

Font colour

When you want to change the font colour within a

spreadsheet, you must select the cell or cells to be

changed. The font colour within a cell is changed

from the

Home tab, within the Font group.

To select the font colour that you require, use the

drop-down palette.

Cell colour and shading

To fill a cell with a solid background colour,

select the cell or cells to be coloured. From the

Home tab,

within the

Font group, select the background colour

that you require using the drop-down palette next to

the fill icon.

203

8.1 Create a spreadsheet

Other fill effects like gradient fill effects using a

combination of two colours and different types

of shading are also available using the

Format

Cells window and the Fill tab.

Pattern fills are created by selecting the

Pattern

Color

using the drop-down palette,

and the style of shading using the Pattern Style

drop-down menu.

Each time you select these options a sample fill is

shown in the

Sample section.

Gradient fills can be located by selecting the

button.

This opens the Fill Effects window, where

different options are available to give a range of

one and two colour gradient fills.

Cell borders

To edit cell borders, highlight the cell or cells you wish

to use, then open the

Format Cells window and select

the

Border tab. Choose the style and weight (thickness)

of the line from the Line group.

Different styles and weights can be applied to the outside

border and the internal gridlines of an area. Click on the

Outline button to apply the chosen style to the outline

of the selected range of cells, or

Inside to apply this style

to the inner gridlines within the highlighted area.

If you wish to apply colour to these lines, select the

Color: using the drop-down palette, then click

on the line or lines that you wish to apply this to in the

Border diagram.

204

8 SPREADSHEETS

In this example, the outside of the highlighted area will be set to a thick dark

blue line and all internal gridlines will be set to a dotted red line. To do this

click on all four internal lines in the

Border section, then click on

Adding comments

Comments or notes can be added to cells to add extra information about

the contents of that cell, without changing the contents or affecting

any calculations. These are added (almost like sticky notes) to a cell by

clicking on the cell with the right mouse button and selecting New

Comment from the drop-down menu. Type your comment or note in

the box and, if it is a comment, it will be attributed to your user name as

the start of a comment thread. If another user edits the comment, their

reply will be added to your comment in the same thread, along with the

date and time.

Use the

button to save your comment. The cell will now appear with a

small comment shape in the top-right corner when seen on screen but the

comment will not show when printed.

Advice

In previous versions of Excel,theseCommentswerecalledNotesandwere

inserted by clicking the right mouse button and selecting New Note. Notes did not

allowotherstoreplyinathread.

Task 8as

Openthefilesavedas Task_8j.

For each employee, calculate the total hours worked, the gross pay, tax due and

net pay. Calculate the totals for columns B to G.

Calculate the percentage contribution in terms of time to the company. For each

cell displaying the percentage time, set the background colour of the cell to:

» orangeifanemployeecontributedbetween12%and18%ofthetotalhours

» redifanemployeecontributedlessthan12%ofthetotalhours,andformat

the text to be white

» greenifanemployeecontributedmorethan18%ofthetotalhours.

SavethespreadsheetasTask_8as.

Place in cell B12 the formula =SUM(B6:B11) and replicate this to cell G12.

Place in cell D6 the formula =B6+C6, which returns the value 24. Place in cell

E6 the formula

=D6*$B$2, which returns the value 244.80. Place in cell F6

the formula =E6*$E$2, which returns the value 97.92. Place in cell G6 the

formula =E6-F6, which returns the value 146.88. Place in cell G7 the formula

=D6/$D$12. Format this cell as a percentage to one decimal place, so that it

returns the value 27.6%. Replicate these formulae (and the formatting) for all

employees. Place in cell B12 the formula

=SUM(B6:B11) and replicate this for

columns C to G inclusive.

Conditional formatting

Conditional formatting is the most efficient method used to change the display

format (usually the font or background colour within a cell), depending upon

the contents of the cell.

205

8.1 Create a spreadsheet

Highlight cells H6 to H11 inclusive as conditional formatting will be applied to

all of them. Select the Home tab, in the Styles group, use the drop-down menu

for the

Conditional Formatting icon.

Select New Rule to open the New Formatting Rule window. Select the

second option for

Format only cells that contain, which changes the layout of

the window. Enter data into the

Format only cells with: section so that it looks

like this.

Note that the 12% and 18% have been entered as their decimal equivalents.

When the rules have been entered, click on the

button to open the

Format Cells window. Select the Fill tab, then double-click on an orange

colour from the palette, then click

. Click again and

the first conditional formatting rule has been set. To add the

second rule, repeat the process. Use the drop-down menu that

contains

between and change it to less than. Change the value

to 0.12 and the format to white text on a red background so

that the Format only cells with: window looks like this.

Click

to set this rule and

repeat the process to add a third

rule, changing the rule to Format

only cells with: greater than 0.18

as green. The formatting rule will

look like this.

The completed spreadsheet looks

like this:

To edit conditional formatting

rules after they have been created,

highlight the range of cells, in the

Home tab, Styles group select

Conditional Formatting, then

choose the last option from the list

Manage the rules. All three

rules created are visible in the

Conditional Formatting Rules

Manager

window.

206

8 SPREADSHEETS

To edit a rule, click on the rule to select it and then click on the

button. This opens the Edit Formatting Rule window, which is identical to the

New Formatting Rule window that we have used before. Save the spreadsheet

as Task_8as.

Advice

You don't always need to use conditional formatting that involves numeric cell

values; sometimes comparing cell contents with text strings or finding duplicate

values is required.

Activity 8t

OpenthefileyousavedasActivity_8c.

Apply coloured backgrounds to cells in the range C7 to C23 to display:

» goldifthecellcontains1st

» silverifthecellcontains2nd

» bronzeifthecellcontains3rd.

SavethespreadsheetasActivity_8t.

8.2Testaspreadsheet

Designing a test plan and choosing your test data are the most important parts

of testing any spreadsheet. All formulae and all validation rules need to be tested

to ensure that they work as you expected them to. If you test every formula

in a spreadsheet thoroughly, the number of possible errors is reduced when

you use the spreadsheet with real data. For formulae containing conditions

(for example IF, COUNTIF, INDEX, MATCH and so on) you must choose

data that will test every part of the condition. If you are testing calculations,

use simple numbers that make it easier for you to check them. Be careful to

test each part of the spreadsheet with

normal data from which you would

expect your formulae to produce appropriate results, with extreme data to test

the boundaries, and with

abnormal data which you would not expect to be

accepted. Carefully check that all formulae work as you expect them to by using

simple test data.

207

8.2 Test a spreadsheet

Example 8.1 Test the validation rules set in Task_8an

Asthereare14studentsyettositthetest,wewillneedtoensurethe

validation rule works for cells C8 to C80. We should really create test plans

for each of these cells. As all test scores must be whole numbers we will

needtotestwithintegeraswellasdecimalvalues,testwithtwonormal

data items and two abnormal data items (decimals). As the highest score is

10 and the lowest must be 0, we need to test both extreme items 0 and 10

and some other selected normal data items. We also need to test with two

abnormal data items that are outside the acceptable range.

You need to create the test plan before entering any data into the

spreadsheet. Test plans should be created for each cell in the range C8 to

C80. A test plan for these validation rules for cell C8 would be similar to that

showninTable8.7.

Test type Validation Cell C8

Data entry Data Expected result Actual

result

Remedial

action

0 Extreme/Normal Data accepted

1 Normal Data accepted

4 Normal Data accepted

8 Normal Data accepted

10 Extreme/Normal Data accepted

-14 Abnormal Validation error message

1.3 Abnormal Validation error message

5.6 Abnormal Validation error message

1004 Abnormal Validation error message

▲ Table 8.7 Sample test plan for Task_8an: cell C8

208

8 SPREADSHEETS

Example 8.2 Test the validation rules set in Task_8ao

Asnewproductsneedtobeaddedtothelist,wewillneedtoensurethe

validation rule works for cells G5 to G130 (or more) and for F5 to F130 (or more,

depending on the range of the entered rule). One example test plan for each

validation rule should be enough. As the only chipset manufacturers used by

this company are AMD and Intel, we will need to test with at least two normal

dataitemsandtwoabnormaldataitems(mis-spelleddata).Asthelistof

supported socket types can be found in the file Socket.csv,weneedtotestwith

three normal data items including those at the start and end of the list and two

abnormal data items (mis-spelled data).

Twotestplansareneededforthesevalidationrules:oneforcellG5,which

would be similar to that shown in Table 8.8, and one for cell F5, which would

besimilartothatshowninTable8.9.

Test type Validation Cell G5

Data entry Data Expected result Actual

result

Remedial

action

AMD Normal Data accepted

Intel Normal Data accepted

AME Abnormal Validation error message

Intem Abnormal Validation error message

▲ Table 8.8 Sample test plan for Task_8ao: cell G5

Test type Validation Cell F5

Data entry Data Expected result Actual

result

Remedial

action

775 Normal Data accepted

AM3 Normal Data accepted

FT1 Normal Data accepted

776 Abnormal Validation error message

1FT Abnormal Validation error message

▲ Table 8.9 Sample test plan for Task_8ao: cell F5

Advice

Therearenoexamplesofextremedataineitherofthesetestsastextdataheld

inalistcannothaveextremes.

For each test that you perform, write down each test data item and the expected

results before trying each number in the cell. Check that the actual result

matches the expected result for every entry. If not, change the validation rule

before starting the whole test process again.

Check you have used the right ranges within formulae by testing that everything

works before using real data in your model. If you find an error during testing,

correct it, and then perform all of the tests again, as one change to a spreadsheet

can affect lots of different cells. It is very important to test extreme data (where

it exists) as it can reduce errors such as greater than being used rather than

greater than or equal to.

209

8.2 Test a spreadsheet

Task 8at

Create this spreadsheet to calculate the surface area of a

closed box.

Test the spreadsheet. Save your test plan as TP_8at.rtf.

Use the finished spreadsheet to calculate the surface area of a

box3.4cmx4cmx2.7cm.

SavethespreadsheetasTask_8at.

Create and save the spreadsheet to appear as shown.

Highlight cells B2 to B4 and add validation like this.

This rule excludes sides with no length or negative

values, as a box cannot have sides with a negative

length.

Four test plans are needed, three for the validation

rules, one for each cell, although only one example has

been shown here in Table 8.10 and one test plan for the

formula in cell B6 which can be seen in Table 8.11.

Test type Validation Cell B2

Data

entry

Data type Expected result Actual result Remedial

action

0.00001 Normal Data accepted

42.6 Normal Data accepted

1000 Normal Data accepted

0 Abnormal Validation error message

-0.001 Abnormal Validation error message

One Abnormal Validation error message

-60 Abnormal Validation error message

▲ Table 8.10 Sample test plan for Task_8at: cell B2

Now we need to plan the test for the formula. The first sizes chosen are for a box

1 cm square. As a closed box has six sides and each is 1 cm

, the total surface

area should be 6 cm

. The second items of test data will be 2 cm sides, again six

sides, each with a surface area of 2 × 2 = 4 cm

, so the total surface area will be

6 × 4 = 24 cm

. The third items of test data will be for a box that is not a cube

with square sides, with a length of 2, width of 2 and height of 3. This means

that the base and top should each be 2 × 2 = 4 cm

and the four sides should be

2 × 3 = 6 cm

, so the total surface area will be 4 + 4 + 6 + 6 + 6 + 6 = 32 cm

The fourth items of test data will have a length of 10, width of 5 and height of 2.

This means that the base and top should each be 10 × 5 = 50 cm

, two sides

should be 10 × 2 = 20 cm

, two sides should be 5 × 2 = 10 cm

, so the total

surface area will be 50 + 50 + 20 + 20 + 10 + 10 = 160 cm

. We should also

include decimal values to test it works for those, so the fifth items of test data

will have a length of 10, width of 1.5 and height of 2. This means that the base

210

8 SPREADSHEETS

and top should each be 10 × 1.5 = 15 cm

, two sides should be 10 × 2 = 20 cm

two sides should be 1.5 × 2 = 3 cm

, so the total surface area will be

15 + 15 + 20 + 20 + 3 + 3 = 76 cm

. The initial plan would look like this.

Test type Formula Cell B6

Data entry in cell:

Expected

result

Actual result

Remedial

action

B2 B3 B4

111 6

22224

22332

10 5 2 160

10 1.5 2 76

▲ Table 8.11 Sample test plan for Task_8at: cell B6

Use the test data from Table 8.10 to test the validation rules. Do they work as

expected? Complete your table as you try each test.

Use the test data from Table 8.11 to test the formula. Does it work as expected?

Complete your table as you try each test. The completed table with the results of

each test is shown in Table 8.12.

Test type Formula Cell B6

Data entry in cell:

Expected

result

Actual result

Remedial action

B2 B3 B4

1 1 1 6 5 Yes – change formula?

2 2 2 24 20 Yes – change formula?

2 2 3 32 28 Yes – change formula?

10 5 2 160 110 Yes – change formula?

10 1.5 2 76 61 Yes – change formula?

▲ Table 8.12 Completed test plan for Task_8at: cell B6 – Version 1

We must now identify where the error has occurred and change the formula.

The first piece of test data is probably the most useful here as it shows that

onlyfive sides have been calculated. Correct the formula in cell B6 so it

becomes

=2*(B2*B3+B2*B4+B3*B4). Attempt all the tests for cell B6 again.

The resulting test plan should look like Table 8.13. Save your test plans as

TP_8at.rtf.

Test type Formula Cell B6

Data entry in cell:

Expected

result

Actual result

Remedial

action

B2 B3 B4

111 6 6No

2222424No

2233232No

10 5 2 160 160 No

10 1.5 2 76 76 No

▲ Table 8.13 Completed test plan for Task_8at: cell B6 – Version 2

211

8.3 Use a spreadsheet

Activity 8u

OpenthefileyousavedasActivity_8s.

CreateatestplantotestthevalidationplacedincellC9.Usethistoperformthe

tests on this cell, complete the test plan and save the test plan as Activity_8u.rtf.

SavethespreadsheetasActivity_8u.

8.3Useaspreadsheet

This section includes searching for data extracts, sorting data, summarising data

and exporting data. We do not need to cover importing data, as we have already

imported data many times in this chapter.

8.3.1 Extract data

Search using text filters

This means getting Excel to search through data held in a spreadsheet to extract

only rows where the data matches your search criteria.

Task 8au

OpenthefileyousavedasTask_8ao.

A customer requires a motherboard manufactured by MSI, that uses an

AMD chipset and DDR3 or DDR4 memory. Extract from all the data only the

motherboards that meet these requirements.

SavethespreadsheetasTask_8au.

Open the file that you saved as Task_8ao and highlight cells A4 to I20, which

is the range of data to be searched, and the headings for each column. Select the

Data tab and find the Sort & Filter group. Click on the Filter icon to display

an arrow in the top-right-hand corner of each column.

To start this task, you need to use this drop-down arrow in the

Manufacturer column, to select those boards manufactured

by MSI.

When you click on the

Manufacturer arrow, a small drop-down

menu appears like this.

In the lower part of the drop-down menu, click on the tick box for

(Select All) to remove all of the ticks from every box. Then tick only

the MSI box, then

and the spreadsheet will not display any

other manufacturer.

212

8 SPREADSHEETS

Use the drop-down menu in the Chipset column and select only those

motherboards with an AMD chipset. What we have done so far is equivalent

toan

AND search using two columns. We have selected only the motherboards

made by MSI AND that have an AMD chipset. For the next stage we are going

to search in a third column, this time the Memory column, and the values that

are required are DDR3

OR DDR4 so we need to tick both the DDR3 and

DDR4 boxes.

The result of this complex search displays 27 possible motherboards that meet

the customer’s requirements.

Save the spreadsheet as Task_8au.

Search using numeric data

Examine Table 8.14 carefully to relate how comparison operators can be

interchanged with text in AutoFilter searches.

Operator Text Example:

= Equal to / Like X = Y X is equal to Y

<> Not equal to X<>Y X is not equal to Y

> Greater than X

> Y X i

s greater than Y

< Less than X < Y X is less than Y

>= Greater than or equal to X >= Y X is greater than or equal to Y

<= Less than or equal to X <= Y X is less than or equal to Y

▲ Table 8.14 Comparison operators and their equivalent text

Task 8av

OpenthefileyousavedasTask _8au.

This list meets the customer’s needs but there are too many items to

select from. Extract from all the data only the motherboards to meet these

requirements. The customer wants to spend less than $130 but does not want

to risk using a cheaper motherboard costing less than $80. Extract from the

results of Task_8au only the motherboards to meet these requirements.

SavethespreadsheetasTask_8av.

Open the file that you saved as Task_8au and select the drop-down menu

for the Price column. For this task it is possible to tick or untick all the

boxes, depending on whether each price listed meets both criteria, but this is

inefficient. Instead, click on

Number Filters.

From the drop-down list, select

Between to open a Custom AutoFilter

window. Enter the two values as shown. Make sure, using the drop-down lists

in the

Price group, to change ‘is less than or equal to’ to ‘is less than’ to match

the wording of the task, like this.

213

8.3 Use a spreadsheet

This search is again a form of AND search as the price must be greater than or

equal to $80 and less than $130. The list of products for the customer will look

like this:

Save the spreadsheet as Task_8av.

Task 8aw

OpenthefileyousavedasTask _8av.

This list meets the customer’s needs, but the customer has been advised that

Model S-7778 (2AEO) is not suitable for their needs. Refine the extract from the

resultsofTask_8ausothatthismodeldoesnotappear.

SavethespreadsheetasTask_8aw.

Open the file that you saved as Task_8av and select the

drop-down menu for the Model column. For this task it

is possible to untick the box for this model, but we will

remove it using a

NOT operator. Select the drop-down

list for model and click on Text Filters.

From the drop-down list to the right,

select

Does Not Contain to open a Custom

AutoFilter window. Use the right-hand drop-down

and scroll down the list of models to highlight S-7778

(2AEO), like this.

Click to refine the list. Save the spreadsheet as Task_8aw.

Search using date and time

Task 8ax

Open the file Flights.csv.

ApassengertravelledonanearlymorningflightinJanuary2022.Theycould

not remember the flight number but knew that they travelled on one of three

days. Extract from this data all flights that departed before 9 a.m. on 16, 17

or 18

January 2022.

Save the spreadsheet as Task_8ax.

214

8 SPREADSHEETS

Open the file, save it as a spreadsheet and highlight cells A1 to E115. Select the

Data tab, find the Sort & Filter group and click on the Filter icon. Use the

drop-down arrow in the Depart_Date column to select the

Date Filters

drop-down menu to open the Custom AutoFilter window.

Advice

Thereisnotscopeinthisbooktocoverallthedifferentfiltersavailablehere,or

in the Text and Number Filter menus. Spend time experimenting with them to find

out how they all work.

Use the drop-down menus and

scroll down to select the start

and end dates for departure like

this.

Accuracy in the exact dates

entered is crucial in order to

produce the correct results. Click

to create the extract.

Use the drop-down arrow in the

Depart_Time column to select

the

Number Filters drop-down

menu and the option for Less

Than… to open the Custom

AutoFilter window. To reduce

typing errors, use the drop-

down list on the right to select

the time value.

Click

to create the list. This will return

these three flights. Save the spreadsheet as

Task_8ax.

Task 8ay

Open the file Flights1.csv.

Identify all flights with a duration of less than three hours that arrived in

February.

SavethespreadsheetasTask_8ay.

Open and examine the file Flights1.csv. This file has the dates and times

together in a single column. Select the

Home tab. In the Number section of

the toolbar, format the contents of the cells in column D as time. Place in cell

D2 the formula

=C2-B2 to calculate the duration for this flight. Replicate this

formula for all flights. Highlight all the cells.

Select the

Data tab, find the Sort & Filter

group and click on the Filter icon. Use the

drop-down arrow in the Duration column to

select the Time Filters drop-down menu to

open the

Custom AutoFilter window. Set

the duration and value like this.

215

8.3 Use a spreadsheet

Click then use the drop-down menu for

the Arrival column to select

Date Filters. Near the

bottom of the drop-down list is an option for

All

Dates in the Period; select this and then February

from the sub-menu. Click

. The results

will be:

Save the spreadsheet as Task_8ay.

Search using substrings

Task 8az

OpenandexaminethefileyousavedasTask_8ao.

A customer requires a motherboard made for gaming. The customer can

remember that the model name she requires starts with 170. Extract from all

the data only the motherboards to meet these requirements.

SavethespreadsheetasTask_8az.

Open and examine the file. You will notice that the only reference to the word

‘Gaming’ is in the Model column. Highlight cells A4 to I120 and select the

Data tab, in the Sort & Filter group, click on the Filter icon. Select the drop-

down arrow for the Model column and the option for Text Filters. From the

sub-menu select Contains....

Enter ‘Gaming’ into the box like this.

As this Custom AutoFilter window will also be used to find the model name

beginning with 170, we

add a second criteria to

this filter. Make sure

that the

AND radio

button is selected rather

than OR and select

begins with as the

second filter type. Enter

the text ‘170’ in the final

box like this.

Click

. The results will be:

To select a criterion that ends with

some text, use the same method

and select

ends with as the filter

type. Save the spreadsheet as

Task_8az.

216

8 SPREADSHEETS

Activity 8v

OpenthespreadsheetyousavedasActivity_8s.

Extract a list of students who attained both Maths and English at grade 1.

SavethespreadsheetasActivity_8v.

Activity 8w

OpenthefileyousavedasActivity_8r.

Extractalistofflightsthatdepartedafter8a.m.oneither7January2022or21

January 2022.

SavethespreadsheetasActivity_8w.

Activity 8x

OpenthefileyousavedasActivity_8q.

Extract all the employees with names beginning with the letter S, who live in the

centre of Bolton or Dundee.

SavethespreadsheetasActivity_8x.

Sort data

Before you sort data, make sure that you select all the data for each item to be

sorted. A common error is to select and sort on a single column and therefore

lose data integrity. Table 8.15 shows examples of correct and incorrect sorting

on students’ names for a spreadsheet containing their test results in Maths and

English. Note how the results for each person have been changed when sorting

without highlighting all the data.

Original data

Sorted correctly with all data

selected

Sorted with only the name

column selected

Name Maths English Name Maths English Name Maths English

Sheila 72 75 Karla 52 75 Karla 72 75

Marcos 64 34 Marcos 64 34 Marcos 64 34

Vikram 61 44 Sheila 72 75 Sheila 61 44

Karla 52 75 Vikram 61 44 Vikram 52 75

▲ Table 8.15 Sorting data with correct and incorrect data selected

Task 8ba

OpenandexaminethefilesavedasTask_8ae.

HidecolumnsL,NandcolumnsBtoIinclusive.Sortthedataintoascending

orderoftown/city,thenascendingorderoffamilyname,thenintoascending

order of given name.

SavethespreadsheetasTask_8ba.

Open and examine the file. Hide the specified columns so that only columns A,

J, K and M are visible. Highlight all visible cells. Select the Data tab, find the

Sort & Filter group and click on the Sort icon.

217

8.3 Use a spreadsheet

This opens the Sort window. As all the data

was highlighted, including the column headings

in row 1, tick the check box for

My data has

headers.

Use the drop-down arrow in the

Sort by

section to select the

Town/City column as

the primary sort.

Check that the right two text boxes contain

‘Cell Values’ and ‘A to Z’ (ascending) for the

sort order. Click on the

Add Level button.

Use similar methods to select the Family_

name column as the secondary sort, checking

that the right two text boxes contain ‘Cell

Values’ and ‘A to Z’ for the sort order. Use

similar methods to select the Given_name

column as the tertiary (third-level) sort,

checking that the right two text boxes also

contain ‘Cell Values’ and ‘A to Z’ for the sort

order. When the sort window looks like this,

click

to complete the sort. Save this

as Task_8ba.

Advice

Datacanbesortedintodescendingorderratherthanascendingorderby

selecting Z to A rather than A to Z in the Order box.

Activity 8y

OpenthefileyousavedasActivity_8b.

Sort the data into ascending order of family name, then into ascending order of

first name.

SavethespreadsheetasActivity_8y.

Pivot tables

A pivot table can analyse and summarise data into a two-dimensional table. It is

used to see patterns and trends in data.

218

8 SPREADSHEETS

Task 8bb

OpenandexaminethefileBusAnalysis.csv.

Analysethisdatatocountthenumberofbusesbetweeneachdestination.The‘buses

from’ should provide the row headings and ‘buses to’ should be the column headings.

Repeatthisanalysis,withthesamedisplaylayout,tocountthenumberofbuses

that were five or more minutes late between each destination.

Use these analyses to display the percentage of buses that were five or more

minuteslateoneachroute.

Show the information from the first analysis in graphical form.

SavethespreadsheetasTask_8bb.

Open and examine the file BusAnalysis.csv. Highlight

the range of cells from B1 to D118 (as column A is not

needed for this task).

Select the Insert tab

from the ribbon and in

the

Tables group, select

the PivotTable icon.

This opens the Create

PivotTable

window.

Make sure that the radio button for New Worksheet is

selected, then click on

Whenever you click on a check box

the field automatically appears in the

Rows box below.

Click on the check box for From and it appears in the Rows box, which is

fine for our purposes.

Click on the check box for To but now you need to drag this field from the

Rows box into the Columns box. Click on the Late box and drag this field

from the Rows box into the Values box. This is displayed as ‘Count of Late’

when in this box and will look like this.

Click on the close icon for the PivotTable Fields window. The pivot table will

appear in a new worksheet. Edit the headings so that it looks like this.

219

8.3 Use a spreadsheet

Excel has counted the number of journeys made from one location to another

during this period of time. You will notice that in cell A3, it has attempted to

give a heading for the pivot table, but this is incorrect, as the count includes

times where the bus was not late. Enter in A3 a new label ‘All journeys’. To

make it easier to understand the pivot table, change the label in A4 to ‘Bus

from:’ and change the label in B3 to ‘Bus to:’.

For the second pivot table, filter the original data in the BusAnalysis sheet so

that only buses that were five or more minutes late are visible. Copy and paste

all columns into a new worksheet

in the workbook (otherwise all

other data will appear in your

pivot table). Highlight all the

data in this new worksheet apart

from column A. Create a new

pivot table as described above

but place the pivot table in cell

A17 of the same worksheet that

contains the first pivot table. It

should look like this.

To display the percentage of buses that were five or more minutes late on each

route, we are going to copy the contents of each cell in the lower pivot table. To

do this, enter into cell A31 the formula

=A17 and replicate this to the right and

down to fill all cells in the range A31 to K41. Do not copy the Grand Totals. To

calculate the first percentage, type in cell B33 the formula

=IF(AND(B19>0,B5>0),B19/B5,""). (Type this formula; do not click in cells

B19 and B5 or the formula will be much more

complex.) This will only calculate the

percentage if both B19 and B5 contain a

number greater than zero. It will remove

division by zero errors or cells displaying 0%.

Format this cell as a percentage and replicate

this to the right and down to fill all cells in

the range B33 to K41. The completed analysis

should look like this.

Pivot charts

The final part of this task requires us to show the information from

the original data in the BusAnalysis sheet in graphical form. Click on

the filter button to remove the filter which we used when creating the

pivot table. Highlight the range of cells from B1 to D118 (as we did

for the first part of Task 8bb). Select the

Insert tab from the ribbon

and in the

Charts group, select the PivotChart icon.

This opens the Create PivotChart window. Make sure the radio button

for a New Worksheet has been selected then click on

. When the

PivotChart Fields pane opens, click on the check box for each of From, To

and Late. The fields will automatically appear in the Axis (Categories) box.

Leave the From field where it is but drag the To field into the Legend (Series)

box and drag the Late field into the Values box. The resultant chart should

look like this.

220

8 SPREADSHEETS

It is easy to see that buses leaving Tawara and Cryette are rarely late compared

to those leaving other towns. Save the workbook as Task_8bb.

Activity 8z

OpenandexaminethefileRoles.csv.

Analysethisdatatocountthenumberofemployeesworkingineachdepartment

at each site. Use the sites for the column headings and the departments for the

row headings.

Display the same information in graphical form.

SavethespreadsheetasActivity_8z.

Import and export data

We learnt how to import data at the start of the chapter. The only input formats

that you are likely to experience are comma separated values (.csv) and text (.txt)

files, and both have similar properties. It is unlikely that your spreadsheets will

be exported into either of these formats, as they do not preserve the formulae

that you have used. However, should you be required to export a spreadsheet

into either of these formats, from the

File tab, use the Save As option and select

the appropriate file type from the drop-down menu.

Export in portable document format

It is more likely that you will have to export the values view of your spreadsheet

into portable document format (.pdf).

Task 8bc

OpenandexaminethefileyousavedasTask_8as.

Exportthevaluesviewofthisspreadsheetasasinglepageinportable

document format.

SavethespreadsheetasTask_8bc.

Open and examine the file. Ensure that the contents of all columns are

fully visible. Select the

File tab, then Export. Click on the Create

PDF/XPS button.

Enter the filename Task_8bc. Set the format as .pdf, then click on

221

8.4 Automate operations with a spreadsheet

8.4 Automate operations with a

spreadsheet

Sometimes the same operations are used repeatedly within a spreadsheet. If this

is the case it is more efficient to record a macro when performing the operations

for the first time, then use the recorded macro to perform the same operations

again.

Task 8bd

OpenandexaminethefilesAshford.csv, Bolton.csv, Cardiff.csv and Dundee.

csv.Eachofthesedatafilesholdsthenumberofhoursworkedbyemployees

over a two-month period. The company wishes to calculate the wage paid

to each employee each week and the total wage bill for each week for each

location, but is insistent on showing the wage column for each week alongside

the hours worked.

SavethespreadsheetasTask_8bd.

This task will require a lot of repetitive operations performed on all four

spreadsheets. As each sheet will have the same functions performed, we can

record a macro while performing the operations on the first sheet, then use this

macro to automate the process for the other three sheets.

Record a macro

Save the file Bolton.csv as an Excel Macro-Enabled Workbook called

Task_8bd. This one has been selected as it has more records than some other

sites. Import each of the other specified files as new worksheets within this

workbook. Select the View tab from the ribbon while in the Bolton worksheet.

In the Macros group, select the drop-down menu using the arrow below the

Macros icon.

Select the Record Macro option from the menu.

This opens the

Record Macro window. Name

the macro with a meaningful name that includes no

spaces, in this case Calculate_weekly_wage_bill.

Enter an appropriate description of the function

of the macro in the Description box, then click

Nothing will appear to happen after the window

closes, but the macro has started recording. Insert

a new column after each column containing

the working hours for each week, starting with

column I. Insert into these new columns formulae

that calculate the wages for each employee using

a rate of $15 per hour. When the formulae have

been entered and replicated into the cells, for each

employee, for each week, select the drop-down menu below the

Macros icon.

Click on the

Stop Recording option to finish recording the macro.

222

8 SPREADSHEETS

Create a macro button

To create a macro button in Excel, select

the File tab from the ribbon, then from the

drop-down menu select Options to open the

Options window. Select Customize Ribbon

from the options down the left-hand side of

the window. In the Main Tabs section, click

on the button for New Tab (Custom), then

click on the Rename button.

Give the tab an appropriate name, in this case

My Macros, then click

. Click on/

highlight

New Group (Custom) and rename

it with an appropriate name, in this case A

level tasks. With this A level tasks group

highlighted, select the Choose commands

from:

drop-down menu.

Select Macros from this list.

Highlight the macro Calculate_weekly_wage_

bill

. Then click on the

button, then

Click on My Macros and

the new macro button has

been added to the new tab

like this.

Move into the Ashford sheet of your workbook.

Click on the macro button that you have just

created. Because there are more employees in

Bolton than Ashford, you will need to delete the extra formulae that are not

required. Repeat this for the other two sheets (extra formulae will not need

deleting from the Dundee sheet). Calculate column totals for the new column

totals individually. Using a macro would require them all to be on the same row

and as each site has a different number of employees the use of a macro may be

less efficient. Save the workbook as Task_8bd.

8.5Graphsandcharts

8.5.1 Chart types

Selecting the most appropriate chart type is often difficult to work out, so it is

helpful to consider the options of a pie chart, a bar chart and a line graph.

223

8.5 Graphs and charts

Pie charts

If you are asked to compare percentage values, a pie chart is often the most

appropriate type because pie charts compare parts of a whole. An example would

be comparing the percentage of males and females in a class.

Bar charts

Bar charts show the difference between different items. A bar chart is traditionally

a graph with vertical bars, but this is called a ‘column graph’ in Excel. To

create a vertical bar chart you would need to use the ‘column chart’ and for a

horizontal bar chart (with the bars going across the page) you would need to

use the ‘bar chart’. An example would be showing the prices of five different

items for sale.

Advice

Donotusestackedcolumnchartsorstackedbarchartsinsteadofbarcharts,as

thesedisplaypercentagesorpartsofawhole.

Line graphs

Line graphs are used to plot trends between two variables. An example would be

plotting the distance travelled on a journey against time. You could then find

any point in time on the graph and be able to read the corresponding distance,

even if the exact distance had not been measured at that time.

Advice

Keepyourchartssimple,donotaddfeaturesthatarenotneeded.Asimplechart

is more efficient and effective.

8.5.2 Create a chart

Selecting the data

To create a chart, you have to highlight the data that you wish to use. This is

easy if it is all contiguous data (where the data is placed together and can be

selected in a single range). If you need to highlight

non-contiguous data, hold

down the <Ctrl> key while selecting your ranges of data (if you do not, the

previously selected data is no longer highlighted).

Task 8be

OpenandexaminethefileBolton2.csv.Calculatethenumberofemployeesand

the average salary for each department. Produce an appropriate chart to display

thenumberofemployeesforeachdepartment.

SavethespreadsheetasTask_8be.

Open the file. Enter in cell B20 the formula

=COUNTIF($B$2:$B$17,A20) and in cell C20 the formula

=AVERAGEIF($B$2:$B$17,A20,$C$2:$C$17). Replicate these

formulae for each department. Highlight only cells A18 to B24.

The highlighted data will be the cells used to produce the graph

and should look like this.

224

8 SPREADSHEETS

The cells containing the column headings (A19 and B19) have been included as

they will be used as the labels in the chart, although they will be edited later to

make them more appropriate.

Selecting the chart type

Decide what type of chart you will need for this task. Look

at the data and decide if it compares parts of a whole, shows

trends between two variables or shows a difference. In this

task the data shows the different numbers of employees in each

job type, so a bar chart is the most appropriate chart type, and

in this case we can use a vertical bar chart.

Create a bar chart

Select the Insert tab and find the Charts group. Select the Vertical bar chart

icon (labelled Column in Excel); to access the drop-down menu to select the

chart type like this.

Advice

TheRecommendedChartsiconwilloftenselectthemostappropriatecharttype,

but not always.

From the 2-D Column section, choose the first option.

The bar chart will be generated and look similar to this:

Advice

Ifyouselectthewrongcharttype,youcanalwayschangethislater.

Formatting the chart

Even though this chart appears to be finished, it does not tell a user clearly what

it represents. To change the chart title, click within the text ‘Count employees’.

Edit this text to give a clear description of the chart, like ‘The number of

employees in each department at the Bolton site’. Each axis should also be given

an axis title; first click on the chart to select it. Axis titles (and many other chart

features) are found in the

Design tab in the Chart Layouts group. Select the

icon for Add Chart Element to get a drop-down menu like this.

225

8.5 Graphs and charts

Select Axis Titles from this menu. Select the option for Primary Horizontal

and type in ‘Department’ to add this. Repeat this operation for Primary

Vertical

and type in ‘Number of employees’ to add the second title. Click the

left mouse button in each title and edit them like this.

Save the spreadsheet as Task_8be.

Task 8bf

OpenthefileyousavedasTask_8be. Produce an appropriate chart to compare

the relative salaries for each department. Emphasise the department with the

highest average salary.

SavethespreadsheetasTask_8bf.

Create a pie chart

Open the file. We have already calculated the

average salaries for each department. This needs

displaying as parts of the whole, suggesting a

pie chart. Highlight the non-contiguous data

holding the

<Ctrl> key while selecting the cells

like this.

Select the

Insert tab and find the Charts group.

Select the Pie Chart icon to access the drop-down

menu to select the chart type. From the

2-D Pie section, choose the first

option.

The pie chart will be generated

and look similar to this.

226

8 SPREADSHEETS

Change the title of the chart to a more appropriate

one like ‘Comparative salaries for each department

at the Bolton site’. The question also requires you to

emphasise the department with the highest average

salary. As this is a pie chart, the best emphasis will be

to withdraw the largest segment from the pie chart.

Click on the segment for Management, continue

holding the left mouse button down and gently drag

the segment to the right so that it is extracted from

the rest of the chart, like this.

In this chart, the legend looks a little unusual at

the bottom of the pie chart. To move it, click on

the chart to select it, then select the

Design tab

in the Chart Layouts group, then select the Add

Chart Element icon. Select Legend from the drop-

down menu and, for this example, select the Right

position.

The legend moves like this.

To add data to the segments of the pie chart, use the Design

tab in the Chart Layouts group, and select the Add Chart

Element

icon. Select Data Labels from the drop-down menu,

then select the More Data Label Options… from the bottom

of the sub-menu.

227

8.5 Graphs and charts

This opens the Format Data Labels pane. Click on the Bar Chart icon

(shown here in green) to open the Label Options tab.

This tab can be used to display the values like this.

For this chart it would be more appropriate to display the percentage

for each segment. Remove the tick from the Value check box by

clicking on it and place a tick in the

Percentage check box. As you

change the tick boxes, the chart changes, so you can constantly check

your changes. The chart will look like this.

Save the spreadsheet as Task_8bf.

Task 8bg

OpenandexaminethefileRain.csv. Create an appropriate graph or chart to

compare the cumulative monthly rainfall for the towns of Tawara and Affrazzi.

SavethespreadsheetasTask_8bg.

Create a line graph

Open the file and highlight cells A1 to C15. Decide what type of chart you

will need for this task. Again, look at the data and decide if it compares

parts of a whole, shows trends between two variables or shows a difference.

In this task, the data shows trends between the date and the total amount

of rainfall that had fallen by that date. Because specific dates are used and

the rainfall is cumulative, a line graph is the most appropriate chart type.

As there are two towns shown in the data, you will make a comparative line

graph using both data sets. Select the

Insert tab and, in the Charts section,

select a Line graph and the top-left icon in the 2-D Line section.

228

8 SPREADSHEETS

The line graph will be generated and look similar to this:

The chart will need appropriate labels, so the text ‘Rainfall in millimetres’ needs

entering as the value axis title and an appropriate chart title like ‘Comparing

cumulative rainfall between the towns of Tawara and Affrazzi’ needs adding.

Add a category axis title of ‘Date’. The finished line graph will look like this:

Save the spreadsheet as Task_8bg.

Task 8bh

OpenandexaminethefileRain.csv. Create an appropriate graph to show a

comparison of the rainfall and average temperatures for each month in Tawara.

Add a second value axis to the chart for the temperature. Label and scale these

axes appropriately. Export the chart in portable document format.

SavethespreadsheetasTask_8bh.

Create a combo chart

Open the file and highlight the dates and data for Tawara;

this is in cells A1 to B15 and D1 to D15. Select the Insert

tab then, in the Charts section, select the Insert Combo

Chart

icon.

229

8.5 Graphs and charts

Use the bottom option to Create

Custom Combo Chart, which allows

you to compare two values using bar

charts and/or line graphs and opens

the

Insert Chart window set to

Combo (Excel attempts to predict the

correct chart type), like this.

Both data series show trends between

two variables (with both rainfall and

temperature plotted against the date),

so using line graphs for both series

would be the most appropriate chart

type. To make this happen, choose the

Chart Type as Line for both series

using the drop-down menus, selecting

the first diagram of a line graph from

the options.

Use secondary axes

It is difficult to read the values for the

temperature, so adding and scaling a

second value axis will make it easier

to read the graph. Click the left

mouse button to place a tick in the check box for

Secondary Axis for the

temperature data series (the one shown in orange).

Your graph will now look similar

to this.

Click on

to create the

chart.

Excel has attempted to scale these

axes, but you are now going to adjust

them further. You will change the

primary axis so that it is set between

0 and 400 and the secondary axis

so that it is set between –4 and 22.

These values have been extracted

from the original data: the total

cumulative rainfall is 381mm

(therefore we will choose 400, so that

the scale can go up in steps of 50);

the temperature changes between –3

and 21 degrees (therefore we will use

–4 and 22 so the scale can go up in

steps of two). For this axis it would be

acceptable to use the values –5 to 25

suggested by Excel.

230

8 SPREADSHEETS

To change the primary axis values, click the right mouse button on the

axis labels and from the drop-down menu select Format Axis.

This opens the Format Axis pane on the right-hand side of the window. In

the Axis Options section, the axis Bounds are set to 0 and 450. We want

the bounds set to 0 and 400, so edit the Maximum boundary to 400.

Press the <Enter> key while hovering over the chart, or click the left mouse

button on the chart to refresh it.

Click the left mouse button on the secondary axis. In the

Format Axis

pane change the axis settings to a Minimum value of –4, a Maximum

value of 22 and, in the Units section, set the Major unit to 2. The Minor

unit will change automatically.

It is important to label these axes appropriately. Label the primary axis

(the left one) ‘Cumulative rainfall in millimetres’ and the secondary axis

‘Average daily temperature’. Label the category axis ‘Date’. Label the chart

with a meaningful title, such as ‘Comparison of rainfall and temperature in

Tawara by <your name>’. These changes should leave the chart looking like

this:

Save the file as Task_8bh.

231

8.5 Graphs and charts

Export graphs and charts

The final step of this Task was to export the chart into portable document

format (a .pdf file). Click on the chart to select it, click on the File tab, select

Export from the menu on the left, then double-click on the button for Create

PDF/XPS

. Enter the File name: as Task_8bh.pdf, then click on

Activity 8aa

OpenandexaminethefileTraffic.csv.

Create an appropriate graph to show a comparison of the maximum and

minimum daily temperatures and the number of cars travelling on a road

each day during the month of January. Add a second value axis to the chart for

the two temperatures. Label and scale these axes appropriately. Export the

completed chart in portable document format.

SavethespreadsheetasActivity_8aa.

Examination-style questions

Tawara Construction is developing a spreadsheet to manage and analyse data about

building projects.

1 Open the file BuildingProjects.csv.

Insertanewrow12andincellA12enterthetextProject 10.

[1]

Replace the word Task with the word Project wherever it appears

in the spreadsheet.

[1]

Enter the following data for Project 10:

Outline plans 21

Planning 30

Ground work 43

Building 37

Fixings 11

[1]

Enter the following dates into these cells: [1]

Cell Date

B3 28 August 2018

B4 16 October 2018

B5 2 November 2018

B6 30 November 2018

B7 1 December 2018

B8 2 December 2018

B9 30 December 2018

B10 5 February 2019

B11 2 March 2019

B12 1 April 2019

5 Deleterow2fromthespreadsheet. [1]

Centrealignandemboldenthecontentsofallcellsinrow1and

all cells in column A.

[2]

PlacefunctionsincellsC2,D2andE2toextracttheday,monthand

year as numeric values from the start date of this project.

[3]

232

8 SPREADSHEETS

8 IncellK2,calculatetheenddatefortheprojectusingthestartdate

and the number of days it will take for outline planning, planning,

ground works, building, and fixings. [2]

9 Format all the date cells in columns B and K into the format dd/mm/yyyy. [1]

10 ColumnLmustdisplaytheenddateoftheprojectfollowedbythe

quarter. For example, the date 21/04/2019 should be shown as

21 April 2019 [Quarter 1].

Insert a formula in cell L2 to display the date in K2 in the required

format using the data from the file Quarter.csv.

Replicate this formula for all events.

[14]

Replicate the formulae used in steps 7, 8 and 10 for all events. [1]

In cell B14, use a function to count the number of events that end in

the first financial quarter of 2018. You may include formulae in the

working column to help you.

Replicate this function for all events.

[7]

Create a header which says Project Completion and a footer which

contains your name and automated fields for today’s date and the time.

[4]

Sort the event data so that the end date of the most recent event is at

the top.

Save the spreadsheet with the filename EQ1.

[2]

After analysing the spreadsheet your manager has noticed that some of the

figures are incorrect. The company counts any business in January, February

and March as taking place in the previous year. He knows that there were

three projects that were completed in the tax year 2018.

For example: in the ‘Full end date’ column, 5 February 2019 is shown as 5 February

2019 [Quarter 4], but this must be shown in the ‘Completion dates during’ column as

2018 [Quarter 4].

15 Correct your formulae to show the correct counts for the Completion

dates during section of the spreadsheet. Do NOT change the data

displayed in the Full end date column.

[18]

Change the header to Project completion – corrected.

Save the spreadsheet with the filename EQ2.

[1]

[Total 60 marks]