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

From word munching to number crunching • In 1978 Harvard grad student Dan Bricklin Spreadsheets: an Introduction to utilized the notion of a ‘ malleable matrix ’ to develop the first computer spreadsheet program Excel • Vastly expanded the range of business and personal computing Computer Literacy • In the same way that a word processor gives a user revolutionary power over text , spreadsheet Lecture 9 software yields unprecedented desktop control over numerical data and tabular information generally CL1 2007/08 1 CL1 2007/08 2 Numerical Manipulations Text manipulation and I/O • Financial projections, budgetary reports, • Sorting, splitting, combining, filtering accounting, statistical analysis…. • Tidying up, error checking • Huge variety of repetitive calculations and • Classifying number bashings made rapidly and efficiently • Enhanced computer simulation for • Input from e.g. Web pages modeling, visualization and decision • Output to many formats making • Input and output to/from other applications CL1 2007/08 3 CL1 2007/08 4 Spreadsheet - functions Spreadsheet Basics • Data input, some validation • A spreadsheet document or ‘worksheet’ • Calculation, Modelling appears on the screen as a grid of numbered • Analysis rows and alphabetically lettered columns • Experimentation • The box representing the intersection of a • Simple database functions row and column is called a cell – Sorting, lookup, filtering • Visualisation, graphs/charts, • Every cell in the grid has a unique address presentation made up of a row number and column letter • Versatile but not ultimate answer to any of these CL1 2007/08 5 CL1 2007/08 6 1

Spreadsheet Grid Structure Basics continued A B C • Cells start out empty – it’s up to you to fill them 1 • In any cell you can enter text , numerical data , or a formula representing a relationship between 2 other cells 3 • Numbers (values) are the raw material the 4 spreadsheet uses to perform calculations 5 • The numbers in a spreadsheet can represent wages, test scores, weather data, polling results – 6 anything that can be quantified 7 CL1 2007/08 7 CL1 2007/08 8 Text More Jargon • Entered text serves to label or classify numerical • Calculations will typically involve the values in data for the user – but it’s just meaningless strings more than one cell of characters to the computer • A group or array of cells is called a range • For example, the label ‘Total Points’ doesn’t tell • You can refer to 1-dimensional and 2-dimensional the computer to calculate the total points – the ranges of cells, e.g. A3:A7 is a 1-D array of five label is just an aid to human readers cells, B3:C7 is a 2-D array of ten cells • Formulae , not text, tell the computer what to • A workbook can have several worksheets of cells calculate CL1 2007/08 9 CL1 2007/08 10 Entering text and numerical data Fitting text into cells • Cells start out empty, so to start crunching you • Text labels can be long , and sometimes you have to put something in them need more room or variety, particularly with • First, click the cell you want to become active column headings • Type in text or numerical data - it will appear in • Formatting options include merged cells, the Formula bar as well text wrap, shrink to fit, vertical alignment • Press [ Enter ] or click the ‘tick’ button to the left • Open the Format menu and choose Cells of the Formula bar • Select the Alignment tab and click option • Text data automatically aligns to the left , desired numerical data to the right CL1 2007/08 11 CL1 2007/08 12 2

Formulas Entering Formulas • Always preface with ‘=’, which tells Excel • Formulas allow you to perform calculations that the cell contains a formula on the values contained in the specified • Example formulas: cells =A7/B6 Divide the value in cell A7 by the • The main operations are quite familiar: value in cell B6 + Add, - Subtract, * Multiply, / Divide, =D22*12 Multiply the value in D22 by 12 % Percentage =C7*25% Calculate 25% of the value in C7 CL1 2007/08 13 CL1 2007/08 14 Spreadsheet 1. you type.. Spreadsheet 1. you see.. A B C A B C 1 Pounds Dollars Euros 1 Pounds Dollars Euros 2 2 1 =A2*1.54 =A2*1.587 1 1.54 1.587 3 2.4 =A3*1.54 =A3*1.587 3 2.4 3.408 3.8088 4 6.66 =A4*1.54 =A4*1.587 4 6.66 9.4572 10.56942 5 1.32 =A5*1.54 =A5*1.587 5 1.32 1.8744 2.09484 6 11.88 =A6*1.54 =A6*1.587 6 11.88 16.8696 18.85356 7 7 =sum(a2:a6) =sum(b2:b6) =sum(c2:c6) 23.26 33.0292 36.91362 CL1 2007/08 15 CL1 2007/08 16 AutoFill AutoSum • Typing in the same formula for different values in • And typing in the formula =sum(A2:A6), the above example is tedious – you might as well =sum(B2:B6), etc., in row 7 was needless as well be a monkey • Instead, just click the cell in which you want the • Autofill does it automatically sum to appear, say A7 • Select cell B2 and position the pointer over the bottom right corner – a small black cross will • Click the AutoSum tool on the Standard toolbar appear • Drag over the range of cells you want totaled and • Click and drag the black cross down the B hit [ Enter ] column, and the formula is applied to each new value CL1 2007/08 17 CL1 2007/08 18 3

Worked example - 2 Halifax Interest account projection Example–2. you type.. Year Accumulated Investment table, Halifax account 0/00000-0 investment Initial Capital 0 £ 100.00 A B C End of year 1 £ 105.00 £170.00 2 £ 110.25 1 Year Accumulated 3 £ 115.76 £160.00 4 £ 121.55 Accumulated investment 5 £ 127.63 £150.00 2 0 100 6 £ 134.01 £140.00 7 £ 140.71 3 1 =C2+C2*5% 8 £ 147.75 9 £ 155.13 £130.00 4 2 10 £ 162.89 £120.00 Interest 5.00% 5 3 £110.00 6 4 £100.00 0 2 4 6 8 10 12 7 5 Year CL1 2007/08 19 CL1 2007/08 20 And apply Autofill to get Chart Wizard A B C • To get a chart corresponding to the above 1 Year Accumulated data, first select the appropriate range of 2 0 100 cells 3 1 105.00 • Then click the Chart Wizard tool 4 2 110.25 • Select the chart type, in this case a line chart 5 3 115.76 • Click a few more buttons and presto - a 6 4 121.55 visual representation is inserted… 7 5 127.63 CL1 2007/08 21 CL1 2007/08 22 Worked example - 2 Another Example of Interest Halifax Interest account projection Year Accumulated Investment table, Halifax account 0/00000-0 investment Initial Capital 0 £ 100.00 • Obviously the specific details of the End of year 1 £ 105.00 £170.00 2 £ 110.25 3 £ 115.76 £160.00 previous chart and graph depend on the 4 £ 121.55 Accumulated investment £150.00 5 £ 127.63 amount of the initial investment and the 6 £ 134.01 7 £ 140.71 £140.00 particular rate of interest, both of which can 8 £ 147.75 £130.00 9 £ 155.13 vary…. 10 £ 162.89 £120.00 Interest 5.00% £110.00 £100.00 0 2 4 6 8 10 12 Year CL1 2007/08 23 CL1 2007/08 24 4

Worked example - 3 Halifax Interest account projection Statistical Functions Year Accumulated Investment table, Halifax account 0/00000-0 investment Initial Capital 0 £ 150.00 • Among the vast library of built-in functions that End of year 1 £ 159.00 £280.00 £270.00 2 £ 168.54 Excel can apply to data are various statistical £260.00 3 £ 178.65 £250.00 4 £ 189.37 £240.00 functions , including: Accumulated investment £230.00 5 £ 200.73 £220.00 6 £ 212.78 • MIN, to return the minimum value for a range of £210.00 7 £ 225.54 £200.00 8 £ 239.08 £190.00 cells £180.00 9 £ 253.42 £170.00 10 £ 268.63 • MAX, to return the maximum value £160.00 £150.00 £140.00 Interest 6.00% • AVERAGE and COUNT £130.00 £120.00 £110.00 • Use the drop-down list beside the AutoSum tool £100.00 0 2 4 6 8 10 12 to display these functions Year CL1 2007/08 25 CL1 2007/08 26 Sorting Sorted • Worksheet data can be arranged in • For more complex sorts open the Data ascending and descending order menu and choose Sort … • Sorting can be based on numbers, dates, • Select the main sort field from the list and alphabetical order, etc click ascending or descending • To perform a simple sort , select any cell in • Select second level sort field…. the relevant column and click Sort • Select third level if required Ascending or Sort Descending on the Standard toolbar • Sorting affects data, so be a little careful CL1 2007/08 27 CL1 2007/08 28 Filtering The IF Function • Selects which rows you see based on • The IF function is used to return one value criteria if the condition you specify is True , and another value if the condition is False • Just a viewing operation, doesn’t affect data • The values returned can be text , numbers , or the result of a formula • Filter on ‘less/greater than’, ‘equals’, ‘contains’, ‘starts/ends with’ AND … • Filter on one column then another CL1 2007/08 29 CL1 2007/08 30 5