Spreadsheets: an Introduction to utilized the notion of a malleable - - PDF document

1

CL1 2007/08 1

Spreadsheets: an Introduction to Excel

Computer Literacy Lecture 9

CL1 2007/08 2

From word munching to number crunching

• In 1978 Harvard grad student Dan Bricklin

utilized the notion of a ‘malleable matrix’ to develop the first computer spreadsheet program

• Vastly expanded the range of business and

personal computing

• In the same way that a word processor gives a

user revolutionary power over text, spreadsheet software yields unprecedented desktop control

• ver numerical data and tabular information

generally

CL1 2007/08 3

Numerical Manipulations

• Financial projections, budgetary reports,

accounting, statistical analysis….

• Huge variety of repetitive calculations and

number bashings made rapidly and efficiently

• Enhanced computer simulation for

modeling, visualization and decision making

CL1 2007/08 4

Text manipulation and I/O

• Sorting, splitting, combining, filtering
• Tidying up, error checking
• Classifying
• Input from e.g. Web pages
• Output to many formats
• Input and output to/from other applications

CL1 2007/08 5

Spreadsheet - functions

• Data input, some validation
• Calculation, Modelling
• Analysis
• Experimentation
• Simple database functions

– Sorting, lookup, filtering

• Visualisation, graphs/charts,

presentation

• Versatile but not ultimate

answer to any of these

CL1 2007/08 6

Spreadsheet Basics

• A spreadsheet document or ‘worksheet’

appears on the screen as a grid of numbered rows and alphabetically lettered columns

• The box representing the intersection of a

row and column is called a cell

• Every cell in the grid has a unique address

made up of a row number and column letter

2

CL1 2007/08 7

Spreadsheet Grid Structure

7 6 5 4 3 2 1 C B A

CL1 2007/08 8

Basics continued

• Cells start out empty – it’s up to you to fill them
• In any cell you can enter text, numerical data, or

a formula representing a relationship between

• ther cells
• Numbers (values) are the raw material the

spreadsheet uses to perform calculations

• The numbers in a spreadsheet can represent

wages, test scores, weather data, polling results – anything that can be quantified

CL1 2007/08 9

Text

• Entered text serves to label or classify numerical

data for the user – but it’s just meaningless strings

• f characters to the computer
• For example, the label ‘Total Points’ doesn’t tell

the computer to calculate the total points – the label is just an aid to human readers

• Formulae, not text, tell the computer what to

calculate

CL1 2007/08 10

More Jargon

• Calculations will typically involve the values in

more than one cell

• A group or array of cells is called a range
• You can refer to 1-dimensional and 2-dimensional

ranges of cells, e.g. A3:A7 is a 1-D array of five cells, B3:C7 is a 2-D array of ten cells

• A workbook can have several worksheets of cells

CL1 2007/08 11

Entering text and numerical data

• Cells start out empty, so to start crunching you

have to put something in them

• First, click the cell you want to become active
• Type in text or numerical data - it will appear in

the Formula bar as well

• Press [Enter] or click the ‘tick’ button to the left
• f the Formula bar
• Text data automatically aligns to the left,

numerical data to the right

CL1 2007/08 12

Fitting text into cells

• Text labels can be long, and sometimes you

need more room or variety, particularly with column headings

• Formatting options include merged cells,

text wrap, shrink to fit, vertical alignment

• Open the Format menu and choose Cells
• Select the Alignment tab and click option

desired

3

CL1 2007/08 13

Formulas

• Formulas allow you to perform calculations
• n the values contained in the specified

cells

• The main operations are quite familiar:

+ Add, - Subtract, * Multiply, / Divide, % Percentage

CL1 2007/08 14

Entering Formulas

• Always preface with ‘=’, which tells Excel

that the cell contains a formula

• Example formulas:

=A7/B6 Divide the value in cell A7 by the value in cell B6 =D22*12 Multiply the value in D22 by 12 =C7*25% Calculate 25% of the value in C7

CL1 2007/08 15

Spreadsheet 1. you type..

=sum(c2:c6) =sum(b2:b6) =sum(a2:a6) 7 =A6*1.587 =A6*1.54 11.88 6 =A5*1.587 =A5*1.54 1.32 5 =A4*1.587 =A4*1.54 6.66 4 =A3*1.587 =A3*1.54 2.4 3 =A2*1.587 =A2*1.54 1 2 Euros Dollars Pounds 1 C B A

CL1 2007/08 16

Spreadsheet 1. you see..

36.91362 33.0292 23.26 7 18.85356 16.8696 11.88 6 2.09484 1.8744 1.32 5 10.56942 9.4572 6.66 4 3.8088 3.408 2.4 3 1.587 1.54 1 2 Euros Dollars Pounds 1 C B A

CL1 2007/08 17

AutoFill

• Typing in the same formula for different values in

the above example is tedious – you might as well be a monkey

• Autofill does it automatically
• Select cell B2 and position the pointer over the

bottom right corner – a small black cross will appear

• Click and drag the black cross down the B

column, and the formula is applied to each new value

CL1 2007/08 18

AutoSum

• And typing in the formula =sum(A2:A6),

=sum(B2:B6), etc., in row 7 was needless as well

• Instead, just click the cell in which you want the

sum to appear, say A7

• Click the AutoSum tool on the Standard toolbar
• Drag over the range of cells you want totaled and

hit [Enter]

4

CL1 2007/08 19

Halifax Interest account projection

Year Accumulated investment Initial Capital 0 100.00 £ End of year 1 105.00 £ 2 110.25 £ 3 115.76 £ 4 121.55 £ 5 127.63 £ 6 134.01 £ 7 140.71 £ 8 147.75 £ 9 155.13 £ 10 162.89 £ Interest 5.00%

Investment table, Halifax account 0/00000-0 £100.00 £110.00 £120.00 £130.00 £140.00 £150.00 £160.00 £170.00 2 4 6 8 10 12 Year Accumulated investment

Worked example - 2

CL1 2007/08 20

Example–2. you type..

5 7 4 6 3 5 2 4 =C2+C2*5% 1 3 100 2 Accumulated Year 1 C B A

CL1 2007/08 21

And apply Autofill to get

127.63 5 7 121.55 4 6 115.76 3 5 110.25 2 4 105.00 1 3 100 2 Accumulated Year 1 C B A

CL1 2007/08 22

Chart Wizard

• To get a chart corresponding to the above

data, first select the appropriate range of cells

• Then click the Chart Wizard tool
• Select the chart type, in this case a line chart
• Click a few more buttons and presto - a

visual representation is inserted…

CL1 2007/08 23

Halifax Interest account projection

Year Accumulated investment Initial Capital 0 100.00 £ End of year 1 105.00 £ 2 110.25 £ 3 115.76 £ 4 121.55 £ 5 127.63 £ 6 134.01 £ 7 140.71 £ 8 147.75 £ 9 155.13 £ 10 162.89 £ Interest 5.00%

Investment table, Halifax account 0/00000-0 £100.00 £110.00 £120.00 £130.00 £140.00 £150.00 £160.00 £170.00 2 4 6 8 10 12 Year Accumulated investment

Worked example - 2

CL1 2007/08 24

Another Example of Interest

• Obviously the specific details of the

previous chart and graph depend on the amount of the initial investment and the particular rate of interest, both of which can vary….

5

CL1 2007/08 25

Halifax Interest account projection

Year Accumulated investment Initial Capital 0 150.00 £ End of year 1 159.00 £ 2 168.54 £ 3 178.65 £ 4 189.37 £ 5 200.73 £ 6 212.78 £ 7 225.54 £ 8 239.08 £ 9 253.42 £ 10 268.63 £ Interest 6.00%

Investment table, Halifax account 0/00000-0 £100.00 £110.00 £120.00 £130.00 £140.00 £150.00 £160.00 £170.00 £180.00 £190.00 £200.00 £210.00 £220.00 £230.00 £240.00 £250.00 £260.00 £270.00 £280.00 2 4 6 8 10 12 Year Accumulated investment

Worked example - 3

CL1 2007/08 26

Statistical Functions

• Among the vast library of built-in functions that

Excel can apply to data are various statistical functions, including:

• MIN, to return the minimum value for a range of

cells

• MAX, to return the maximum value
• AVERAGE and COUNT
• Use the drop-down list beside the AutoSum tool

to display these functions

CL1 2007/08 27

Sorting

• Worksheet data can be arranged in

ascending and descending order

• Sorting can be based on numbers, dates,

alphabetical order, etc

• To perform a simple sort, select any cell in

the relevant column and click Sort Ascending or Sort Descending on the Standard toolbar

CL1 2007/08 28

Sorted

• For more complex sorts open the Data

menu and choose Sort…

• Select the main sort field from the list and

click ascending or descending

• Select second level sort field….
• Select third level if required
• Sorting affects data, so be a little careful

CL1 2007/08 29

Filtering

• Selects which rows you see based on

criteria

• Just a viewing operation, doesn’t affect

data

• Filter on ‘less/greater than’, ‘equals’,

‘contains’, ‘starts/ends with’ AND …

• Filter on one column then another

CL1 2007/08 30

The IF Function

• The IF function is used to return one value

if the condition you specify is True, and another value if the condition is False

• The values returned can be text, numbers, or

the result of a formula

6

CL1 2007/08 31

For Example

• A worksheet might contain end of term

exam results for CL1 students

• If a student has, say, 35% or more on the

exam, a Pass will be awarded, and if less than 35% they get a Fail

• Excel can do the work automatically with

the use of a comparison operator

CL1 2007/08 32

Exam Example (imagine 200+ names entered)

7 6 Etc. 5 99 Smith, F. 4 22 Jones, C. 3 75 Bloggs, B. 2 Result Percent Name 1 C B A

CL1 2007/08 33

Comparison Operators

• The operators include

= (equal to), <> (not equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to)

• In the Pass/Fail case, select first cell in the

‘Result’ column, then click More Functions on the AutoSum drop-down list (or click Insert Function button on left side of Formula bar

CL1 2007/08 34

Comparisons Continued

• The IF function belongs to the Logical category
• Enter the condition in the Logical test field, in this

case B2>=40

• Specify the value if the condition is True, in this

case Pass, and the value if it is False – in this case Fail

• Click OK and then AutoFill the Result column:

200+ Passes and Fails will appear automatically

CL1 2007/08 35

Relative and Absolute Addresses

• As we’ve seen, when you AutoFill or copy

a formula, the cell addresses used in it change automatically, relative to the position the formula is copied to

• So by default, the cell addresses used in

formulas are relative addresses

• But this isn’t always what you want

CL1 2007/08 36

Absolutism

• Sometimes you want to keep one or both

the coordinates of a cell address constant

• To do this, type the $ sign in front of each

coordinate that you don’t want to change

• For example:

$C$1 - neither coordinate will change

7

CL1 2007/08 37

Absolutism

C$1 - the column will change if you copy the formula across columns (row is fixed) $C1 - the row will change if you copy the formula down rows (column is fixed) C1 - both coordinates will change relative to its new position (the default)

CL1 2007/08 38

Spreadsheet – example 4

=A8*euro =A8*doll 8 7 7 6 =A5*C$2 =A5*B$2 6 5 =A4*C$2 =A4*B$2 2 4 =A3*C$2 =A3*B$2 1 3 1.587 1.54 rate 2 Euros Dollars Pounds 1 C B A

Not C2, C3, C4 …

CL1 2007/08 39

Spreadsheet – example 5

8 7 =$A6*C$2 =$A6*B$2 7 6 =$A5*C$2 =$A5*B$2 6 5 =$A4*C$2 =$A4*B$2 2 4 =$A3*C$2 =$A3*B$2 1 3 0.63 1.54 rate 2 Euros Dollars Pounds 1 C B A

CL1 2007/08 40 B C D E F G H Relative addressing: copy cell D6 to D9, D10 ..: copy cell D6 to H6 5 B5 F5 6 D6 ref to B5 H6 ref to F5 7 8 B8 9 B9 D9 ref to B8 10 D10 ref to B9 Absolute addressing copy cell D6 to D9, D10 ..: (on row): 5 B5 F5 6 D6 ref to B$5 H6 ref to F$5 7 8 9 D9 ref to B$5 10 D10 ref to B$5 Absolute addressing copy cell D6 to D9, D10 ..: (on row and column): 5 B5 6 D6 ref to $B$5 H6 ref to $B$5 7 8 9 D9 ref to $B$5 10 D10 ref to $B$5

drag drag drag drag drag drag