preparatory mathematics: using spreadsheets for data ... · prep mathematics computer lab tasks...

Prep Mathematics Computer Lab Tasks 2015

1

Preparatory Mathematics: Using Spreadsheets for data representation and summary statistics


2

Section 1: Charting Data as a Histogram

Example 1 The number of defective items on a production line on 20 successive days is given below. 12 14 9 13 11 15 10 12 14 13 12 11 13 12 15 9 14 12 13 10 Construct a frequency table in an excel worksheet and hence plot a histogram of the data. Solution Having first widened the columns A and B, fill in the frequency table derived from the data above in cells A3 to B10

X (Defective Items) f (Number of Days)

9 2

10 2

11 2

12 5

13 4

14 3

15 2

Highlight the cells B4 to B10

Choose the Insert tab and select Column from the Charts subgroup. Select the first of the 2d columns, and you will see the chart shown below right.


3

To get the correct X-axis labels: Right click on chart and choose Select Data:

This will bring up:

Click edit under Horizontal (Category) Axis Labels, then select A4 to A10

Click OK twice to close the dialogue boxes and you will see the graph:


4

As discussed in lectures for a useful Histogram 1. Your graph must have a title. 2. You must label each axis. 3. You must put units on each axis. 4. If possible indicate where the data comes from in a footnote.

To do this the best thing to do is to click on any part of the graph so that Chart Tools appears at the top of your window, and select the Layout tab

To edit the Chart title, choose Chart Title -> Above Chart and then edit this to say Defective Items per Day To edit the Category X axis title, choose Axis Titles -> Primary Horizontal Axis Title -> Title Below Axis and then edit this to say X (Defective Items) To edit the Value Y axis title, choose Axis Titles -> Primary Vertical Axis Title -> Rotated Title and then edit this to say f (Number of Days) The chart now appears as:

To get rid of the Series 1 legend In the chart right click on the Series 1 legend and select Delete select Delete from the drop down menu that appears.

The chart now appears as:


5

Now fill in the summary sheet below to recap on how draw a histogram: 1 2 3 4 5 6 7 8 9 10 11 Changing aspects of the chart: Sometimes it is necessary to change aspects of the graph such as scale etc. In general if you RIGHT CLICK on the part of the chart you wish to change a drop down menu will appear. For example To change the scale of an axis: Right click on the axis (for example, we will right click on the Y axis), and select Format axis option

You then have the option to change the maximum or minimum values and also change the space between the ticks on the axis and where the X and Y axes cross. Rename your worksheet Histogram by right-clicking on Sheet 1 at the bottom of your window and choosing Rename


6

The chart will now appear as follows with the units on the vertical axis now increasing in 2’s:

Experiment with changing the other options on this Format Axis menu and see how the chart changes.


7

Example 2 The salaries (in €000’s) of 50 employees in a large corporation are given below. 10.2 35.7 12.1 19.2 17.5 44.21 15.5 22.3 25.6 28.6

21.2 10.2 33.76 34.12 41.65 29.43 21.0 34.47 38.72 32.12

23.49 16.61 17.12 49.16 55.12 39.17 35.63 41.25 37.73 29.15

43.78 25.55 37.76 18.81 49.12 38.71 33.34 56.72 30.0 47.76

42.23 62.21 68.37 11.2 27.89 39.87 52.3 41.23 19.54 28.87

From a grouped frequency table produce a Histogram of the data on Sheet 1 workbook of a new spreadsheet. Rename this Sheet 1 workbook as Histogram Salaries. From the histogram find the modal class of the salaries data. Solution: In class we used tallies to produce a Grouped Frequency Table as follows:

X (Salaries in €000’s) f (No. of Employees)

From 10 to under 20 11






Open a new workbook and before doing anything else save it as GroupedData. On the worksheet Sheet 1 widen the columns A and B and then fill the table above into the spreadsheet the starting in cell A1. (you can paste the data from the softcopy of this document on the moodle site). Rename the tab for the workbook to be Histogram Salaries . When finished the worksheet should look like:

Now proceed as in section 1 to use this table to produce a histogram of this grouped data, labelled appropriately and hence calculate the modal class.


8

Practice Example (optional): A group of 25 people are asked for their weight to the nearest lb. Here are the answers. 145, 143, 161, 156, 159, 159, 154, 153, 167, 155, 151, 146, 148, 160, 134, 143, 155, 157, 142, 171, 146, 163, 161, 153, 172 1. Fill in the Grouped Frequency Table for these data is below.

X (Weights in lbs) f (frequency)

From 130 to under 140





2. Input this table into Excel In an Excel workbook: 3. Create a histogram for this table with labelled axes and a Title. What is the modal class weight of the data? Save your workbook to your file area.


9

Section 2: Calculating Summary Statistics Example 1 (Calculate the Median of a set of data) The salaries (in €000’s) of 50 employees in a large corporation are listed opposite. Construct a spreadsheet that calculates the mean number of defective items per day and also the standard deviation for the data. Note:

The fill handle on the bottom right-hand corner of the active cell

is used to copy the contents of that cell to other cells.

10.2

35.7

12.1

19.2

17.5

44.21

15.5

22.3

25.6

28.6

21.2

10.2

33.76

34.12

41.65

29.43

21.0

34.47

38.72

32.12

23.49

16.61

17.12

49.16

55.12

39.17

35.63

41.25

37.73

29.15

43.78

25.55

37.76

18.81

49.12

38.71

33.34

56.72

30.0

47.76

42.23

62.21

68.37

11.2

27.89

39.87

52.3

41.23

19.54

28.87

Fill Handle


10

Solution: Open a new workbook, click on the first worksheet and change the tab name to Salaries Medians. Widen the columns A, B in the worksheet. When constructing a spreadsheet with calculations it is very important to use good headings and comments so that somebody else using the spreadsheet (after this described as the user) is aware of what is being done. In this case in the cells A1 to B1 fill in the following as headings (they are what we used in class):

List Position on list Reverse Position on list Paste the data from the softcopy of this document on the moodle site into cells A2 to A51 columns. Rename the tab on the worksheet as Median. The top of the spreadsheet now looks like :

Next highlight the cells from A2 down to A51. From the Home Tab

select the tiny triangle on the Sort&Filter option and the following appears:

Select Sort Smallest to Largest and then the following appears:

Make sure you click on Continue with the current selection and then click Sort


11

The data will then appear sorted as below:

Next we wish to put a number for the position of each piece of data in the column B. The good news is that we don’t need to write the numbers 1 to 50 in the cells B2 down to B51. The quickest way to do this is to fill the numbers 1 and 2 in cells B2 and B3. Then highlight the TWO cells together and catch with the mouse the fill handle which is a little black box that has appeared in the bottom right of cell B3.

Pull and drag this down to cell B51 and excel will have picked up on the pattern 1, 2 and will fill in the numbers 1 -50 in the cells B2 to B51 and so the top of the spreadsheet appears as follows:

You are now in a position to proceed as we did in class to pick out the middle of the list and calculate the median.


12

Next we wish to put a number for the ‘reverse’ position of each piece of data in the column C. The good news is that we don’t need to write the numbers 50 to 1 in the cells C2 down to C51. The quickest way to do this is to fill the numbers 50 and 49 in cells C2 and C3. Then highlight the TWO cells together and catch with the mouse the fill handle which is a little black box that has appeared in the bottom right of cell C3 and pull and drag this down to cell C51. So the top of the spreadsheet appears as follows:

You are now in a position to proceed as we did in class to pick out the middle of the list and calculate the median. The median is : _______________________ (Here it is the average of_______________AND _________________.) Quick Check The last couple of pages have really been about sorting data, which is an important aspect of data handling. For this data set (sorted or not!) you can use the formula =median(A2:A51) to calculate the median. Try it!

Learning to learn exercise: Write a summary “how to list” for how to use a spreadsheet to calculate the median of a set of complete data.

1 Construct a spreadsheet to calculate the mean and standard deviation from a set of complete data (using formulae).


13

Example 2 (Calculate the Mean and Standard Deviation of a set of data) The salaries (in €000’s) of 50 employees in a large corporation are listed opposite. Construct a spreadsheet that calculates the mean number of defective items per day and also the standard deviation for the data.

10.2

35.7

12.1

19.2

17.5

44.21

15.5

22.3

25.6

28.6

21.2

10.2

33.76

34.12

41.65

29.43

21.0

34.47

38.72

32.12

23.49

16.61

17.12

49.16

55.12

39.17

35.63

41.25

37.73

29.15

43.78

25.55

37.76

18.81

49.12

38.71

33.34

56.72

30.0

47.76

42.23

62.21

68.37

11.2

27.89

39.87

52.3

41.23

19.54

28.87

Summary of Method from lectures:

A. First we calculate the mean

B. Next subtract the mean away from each number in the original set.

C. Now square each of these “deviations” from the

mean.

D. Next calculate the standard deviation of the set of numbers by finding the square root of the average (mean) of these squared deviations

sx x

n

e j2


14

Solution: Open a new workbook, click on the first worksheet and change the tab name to Salaries Statistics. Widen the columns A, B, C, D in the worksheet. When constructing a spreadsheet with calculations it is very important to use good headings and comments so that somebody else using the spreadsheet (after this described as the user) is aware of what is being done. In this case in the cells A1 to D1 fill in the following as headings (they are what we are going to do to parallel what was done in class):

x meanx (x-meanx) (x-meanx)^2

Note that the headings in class would have looked like:

x x xx x xd i2

Paste the data from the softcopy of this document on the moodle site into cells A2 to A51 columns. The top of the spreadsheet now looks like:

Step A. Calculate the mean of the salaries:

1. Add up the salaries: In cell A52 type in the formula:

=SUM(A2:A51) In the cell A53 fill in the text

is sum of the x so that a user knows what the number in cell A53 means.


15

The bottom of the spreadsheet where the data ends now looks like:

2. To find the mean salaries we want to divide the sum of the salaries by the number of employees ( in this case 50)

In the cell E2 fill in the text

Mean = so that the user knows what is in the next cell. In the cell E3 fill in the formula:

=A52/50 This calculates the mean value of 50 salaries. The top of spreadsheet now looks like :

Step B. Find the difference of each salary from the mean

1. Fill in the mean of x into each of the cells in column B.

To do this First type

=$E$3 in cell B2.

Next using the fill handle pull drag the contents of cell B2 all the way down to B51 to copy it into the cells below B2. (What the $ signs do is to lock on to the cell E3 which contains the mean value.)

The spreadsheet now looks like:


16

2. Subtract the mean salary from each of the salaries

In cell C2 fill in the formula:

= A2 - B2 Next using the fill handle pull drag the contents of cell C2 all the way down to C51 to copy it into the cells below C2. (this works out the difference from the mean for each of the salaries) Note the way the values are all different and some are positive and some are negative. The spreadsheet now looks like:

Step C: Square the differences between the mean salary and each of the salaries: In cell D2 fill in the formula:

= C2^2 Next pulling the fill handle drag the contents of cell D2 all the way down to D51 to copy it into the cells below D2. (this works out the square of the difference from the mean for each of the salaries) Note the way the values are different and positive. The spreadsheet now looks like:

Step D: Calculate the Standard Deviation 1. Sum the differences between the mean salary and each of the

salaries:

In cell D52 fill in =SUM(D2:D51)

Also in cell D53 fill in the text Is Sum of (x-meanx)^2

so that the user knows what has been calculated in cell D52.

The bottom of the spreadsheet now looks like:


17

2. Calculate the standard deviation of the salaries finding the square

root of the average (mean) of these squared deviations:

sx x

n

e j2

In cell E4 fill in the text

Standard deviavation =

so that the user can see easily what is to be calculated in cell F5 and the formula used to do the calculation.

In cell E5 (to finish calculating the standard deviation using the formula we did in class) fill in

=sqrt(D52/50) or =( D52/50)^0.5

The top of spreadsheet now looks like:

Viewing Formulae: To check the formulas etc. if you toggle to the formula view and you should see: (see footnote 3 page 29 for how to TOGGLE views)

Notice that the cells in which data or text was entered does not change and you can easily see the formulas being used. (Note that sometimes column widths etc have to re-adjusted after toggling between views).

Learning to learn exercise: Write a summary “how to list” for how to use a spreadsheet to construct a spreadsheet to calculate the mean and standard deviation from a set of complete data (using formulae).


18

Footnote 1:

Footnote 1 Filled in summary on how draw a histogram:

1 Widen two adjacent columns in a spreadsheet.

2 Fill in frequency table WITH TITLES in the two columns

3 Highlight the frequency data

4 Select the first of the 2d Column chart type from the Insert Tab

5 To get the correct X-axis labels right click on the chart and choose Select Data 6 Under the Horizontal ( Category) Axis labels press the Edit button and then select the cells containing the class categories( X) 7 Click on any part of the graph so that Chart Tools appears at the top of your

window, and select the Layout tab

8 To edit the Chart title, choose Chart Title -> Above Chart and then change the text in the title above the chart to an appropriate name.

9 To edit the Category X axis title, choose Axis Titles -> Primary Horizontal Axis Title

-> Title Below Axis and then change the text in the horizontal axis title an appropriate name.

10 To edit the Value Y axis title, choose Axis Titles -> Primary Vertical Axis Title ->

Rotated Title and change the text in the vertical axis title an appropriate name.

11 Save the workbook.


19

Footnote 2: Some basics on Excel 2010 Starting Excel

(1) Click Start at the bottom left of the screen

(2) Select Programs

(3) Select Microsoft Applications

(4) Select Microsoft Excel Excel will open a blank Workbook. Each Workbook will have several Worksheets Examine this workspace and look at the figure below to see some of the names given to various sections of the screen.

Figure 2 - Microsoft Excel Screen

To start with some important components are detailed below in Figure 3 Figure 3 There is a box surrounding a particular cell which means this cell is

currently selected or active. The address of the active cell appears in the reference area (also called the Name Box)above column A. In Figure 3, the active cell is A1.

Cells

Active Cell

Fill Handle

Formula Bar


20

The formula bar displays the contents of the active cell. The fill handle on the bottom right-hand corner of the active cell is used

to copy the contents of that cell to other cells.

The main part of the window contains the current worksheet - the

spreadsheet that is being edited. An Excel worksheet has-wait for it!- 264 columns and 65,536 rows. Thus, you have plenty of space to work in!

Examining Ribbon Tab Menus Before delving deeper into the particulars of creating and formatting a particular spread sheet we will take the time to examine the several different ways to access commands and functions in Excel. Quick Access Toolbar The Quick Access Toolbar at the top left corner of the excel window contains shortcuts for Save, Undo, Redo, Quickprint

IF THERE IS A SYMBOL BESIDE THE BUTTON ON ANY MENU THIS MEANS THAT THERE IS A FURTHER DROP-DOWN MENU CONTAINING MORE FUNCTIONS. The File Tab The File Tab located in the upper left-hand corner of the program window, replaces the File menu in previous versions of Microsoft Excel. The Office Button menu contains basic file management commands, including New, Open, Save, Save As, Print and Close.

Fill Handle


21

Ribbon Tab Menus

All the other functions in Excel 2010 are organised into Tab Menus. The most commonly used menus are Home, Insert, PageLayout, Formulas, Data. Briefly examine these five menus. (note the similarities and differences with MS-Word).

Home

This contains most functions to do with formatting cells, inserting and deleting rows and columns, sorting data. Also copying and pasting ( under the Clipboard section)

Insert

This contains functions important to us in terms of inserting graphs in particular.

Formulas

This contains functions important to engineers in particular about using the Excel Function Library of pre-defined mathematical functions.


22

Data.

This contains functions important to engineers regarding sorting, filtering and importing data from experimental work.

PageLayout

This contains functions needed to format print-outs from Excel.

The Home Menu Tab Groups

The icons on each tab are grouped in accordance with their function.

Looking at some of the Home Menu tab groups

Cells:

Number

This symbol allows you to increase or decrease the number of decimals on display in any cell

Pay particular attention to Currencies

Decimals etc

This drop down menu contains options for how the cells store the numbers in different formats: eg text, decimal numbers, dates, scientific notation. Place a number in cell A1 and then change the type of display of the number by selecting number

types from this drop-down menu and see the effect.

The Format drop down menu contains a lot of useful functions to do with cells and their widths etc.


23

Editing

Formulae

Pressing the fx button on the formula bar

will bring up a dialogue box which gives access to all kinds of statistical, mathematical and other functions

The Autosum button : pick a range of cells, and click this to get the sum of all of them.

Click on the drop down menu on the

Sort& Filter button and see the Sort AZ button which sorts a list into Ascending

order; the AZ button into Descending order.

Click on the drop down menu on

the Autosum button button and see the different ready-made functions available.

MAX or MIN: the biggest (or smallest) number in a range.

COUNT: the total number of entries in the cells you’ve selected

AVERAGE:


24

Saving a Workbook: The first thing you should always do when working in excel is to save the workshhet you are working on!Workbooks are saved in a similar manner to word documents. Saving for the first time: Click on the Save as option from the File tab, navigate to the folder you wish to save the workbook (excel file) in and save it using an appropriate name ( you can only use letters and numbers in the names).

Important Note that certain symbols are not allowed in file names e.g , . \ / ! ? and so on. As a rule, do not put symbols in your file names!

It is recommended that you save your work safely as you go.


25

Selecting parts of a spreadsheet

Before looking at formatting a spreadsheet we will first look at how to select various parts of an active worksheet.

To select an entire column of cells, Click the letter at the top of the column.

To select the entire worksheet, Click the button to the left of the heading for column A. To select an entire row, Click the number to the left of the row. You may drag the mouse to select multiple columns and rows To select a (rectangular) chunk of the worksheet Click the mouse pointer on the top-left cell of the chunk, hold the mouse

button down, and move the pointer-which looks like a over the cells to the bottom-right cell of the chunk to be selected. Release the mouse button when all the cells have been covered.

To select two separate areas of the worksheet

Select one area, then hold the CTRL key while selecting the other area.


26

Changing the name of the worksheet tab

We can make our spreadsheet much more user friendly by changing the tab name on the active worksheet. To do this you can either

1 double click on the tab at the bottom of the worksheet which contains the name ‘Sheet 1’ OR

2 Right click on the name tab and choose rename from the menu that appears

The tab should become highlighted in black and you can now enter any sensible name you want on the sheet tab. Certain symbols are not allowed in sheet names like / \ , . ! ? and so on. Avoid symbols in sheet names!

Moving or copying worksheets

Move or Copy sheets WITHIN the current file: Method 1. Right Click on the Worksheet tab name and choose Move or Copy from the menu that appears

Click OK and a copy of the sheet you copies will appear before sheet 3 with (2) at the end of it. You can now re-name the worksheet by clicking on the Worksheet tab and re-naming appropriately. There are two better methods to do this below. We have shown you this one as it is the only way to copy sheets between workbooks.

Highlight Sheet 3

and click on the

Create a copy box


27

Method 2:

On the Home tab, in the Cells group, click Format, and then under Organize Sheets, click Move or Copy Sheet. Method 3:

To move sheets within the current workbook, click on the tab of the sheet and drag the selected sheets along the row of sheet tabs. Let go when you’ve got to the right place.

To copy the sheets, hold down CTRL, and then drag the sheets; release the mouse button before you release the CTRL key.

Move or Copy sheets TO A NEW file:

Right Click on the Worksheet tab name and choose Move or Copy from the menu that appears i.e. click Move or Copy Sheet.

In the “To book” box, click the workbook to receive the sheets. To move or copy the selected sheets to a new workbook, click new

book.

In the “Before sheet” box, click the sheet before which you want to insert the moved or copied sheets.

To copy the sheets instead of moving them, select the Create a copy check box.


28

Some Editing

Spreadsheets also permit particularly flexible editing facilities allowing us to radically restructure an existing worksheet without having to re-type it from scratch or redo calculations!. In this section, we'll look at typical spreadsheet editing actions. To insert a new column to the left of another column,

Select the column by clicking on the letter on the top of the column. Then right click and from the menu that appears choose Insert

Or select any cell on that column and then from the Home Tab in the Cells group from the Insert drop down menu select Insert Sheet Columns. To insert a new row above another row,

Select the row by clicking on the number at the end of the row. Then right click and from the menu that appears choose Insert or select any cell on that column and then from the Home Tab in the Cells section from the Insert drop down menu select Insert Sheet Rows. To copy one (rectangular) section of spreadsheet to another section,

Select the cells then either right click and from the menu that appears

choose Copy or choose from the Clipboard section of the Home menu tab. The border of the selected square of cells flashes. Select the top-left cell of the new position within the worksheet. Either right click and from

the menu that appears choose Paste or choose from the Clipboard group of the Home menu tab. The cell values are copied to the new location. To move one (square) section of spreadsheet to another section, Select the cells then either right click and from the menu that appears

choose Cut or choose from the Clipboard group of the Home menu tab. The border of the selected square of cells flashes. Select the top-left cell of the new position within the document and right click and from the menu

that appears choose Paste or choose from the Clipboard group of the Home menu tab. The cell values are moved to the new location. Other ways to copy to adjacent cells: We can copy contents of individual cells in a simpler and quicker way than "copy-and-paste." To copy the contents of a single cell to other (adjacent) cells

Click the cell.

Move the cursor over the fill handle, that is, the dot at the bottom right-hand corner of the active cell. (The cursor changes to a smaller

plus sign +)


29

Click the mouse and hold the button down.

Drag the cursor over the cells to be copied into and release the button.

Entering data Entering data is the same as entering headings, although it is the last activity to be performed after the spreadsheet has been set up and formatted.

Entering formulae The strength of spreadsheets is their ability to perform sophisticated calculations at lightning speed. We can specify exactly what these calculations are by entering formulae into the spreadsheet.

With a formula, you must place an equals sign ('=') at the beginning of it.

Formulae are built up using the usual arithmetic operators (for example, +, -, *, /), and by using a whole host of functions provided by Excel for performing statistical, financial and engineering calculations to mention

but a few (recall button on the standard toolbar). The general form of these functions is the same: = FUNCTION NAME (first cell : last cell) Thus, to calculate the average of the data in cells B4 through D10 we'd use the AVERAGE function: =AVERAGE(B4:D10) Figure 4 lists some of the functions we'll be using. (Full details of all functions can be acquired from the Help menu.) Function What to type (examples) What it does

Adding =B4+B5+B6+B7+B8+B9 or, alternatively =SUM(B4:B9)

Adds values stored in cells B4 to B9

Subtracting =D5-E3 Subtracts value in E3 from value in D5

Multiplying =B4*B5*B6*B7*B8*B9 or, alternatively =PRODUCT(B4:B9)

Multiplies values stored in cells B4 to B9

Dividing =F2/E3 Divides value in F2 by value in E3 (be careful of dividing by zero)

Average =AVERAGE(B4:B9) Finds average of values in square of cells B4 to B9

Minimum =MIN(B4:B9) Finds minimum value in B4 to B9

Maximum =MAX(B4:B9) Finds maximum value in B4 to B9

Figure 4- Some useful Excel functions


30

Footnote3: Viewing Formulae (instead of Values):

Shortcut for switching between displaying formulas and their values on a worksheet

Press CTRL and the + ` (grave accent) key

(This is below the Esc and beside the 1 key on the keyboard)

This is a TOGGLE key so pressing CTRL again will return the view to the previous view which was of the numbers.

OR On the Formula tab menu click the

Show Formulas button on the Formula Auditing section of menu tab This is a TOGGLE button so clicking it again will return the view to the previous view which was of the numbers.

Exercise: 1. Open a new workbook. 2. In cell A2 on Sheet 1 type in the number 6. 3. In the adjacent cell B2 type in the following:

= 20*A2 – A2/3

The number 118 should appear in cell B2.

4. Using the instructions given above make the formula appear in the cell.

5. Now using the instructions given above change the spreadsheet back to regular view. The number 118 should now be visible in cell B2 again.

Regular view Formula view:

http://en.wikipedia.org/wiki/File:KB_Windows_Ireland.svg

http://en.wikipedia.org/wiki/File:KB_Windows_Ireland.svg

preparatory mathematics: using spreadsheets for data ... · prep mathematics computer lab tasks...

Documents