Analyzing and Viewing Weather Statistics in Excel Using

Analyzing and Viewing Weather Statistics in Excel Using data from Accu. Weather. com CSC 152 (Blum) 1

Browse to https: //www. accuweather. com. Enter your city and hit Enter or click on magnifying glass icon. I am using Chrome, but in the past I have found that copying and pasting data from a webpage to Excel tends to work better with Internet Explorer. CSC 152 (Blum) 2

Click on the Month tab CSC 152 (Blum) 3

Click on the previous month to get a full month’s worth of data CSC 152 (Blum) 4

Switch from calendar view to a list view (actually both are tables) CSC 152 (Blum) 5

Highlight the data, right click and choose Copy from the context sensitive menu CSC 152 (Blum) 6

Right click in Cell A 1 in Excel and choose Paste (Match destination Formatting) CSC 152 (Blum) 7

Result of the paste. CSC 152 (Blum) 8

Save the file. We need to clean up this data. The first problem occurs because of the unit IN and °. A convenient standard is to place unit in the headers of a column and not in each individual cell. We would have been saved a few steps if those who made this table had followed that convention. CSC 152 (Blum) 9

First copy the degree symbol by highlighting it in the formula bar, right clicking and choosing Copy CSC 152 (Blum) 10

Then on the Home tab, click on Find & Select. Choose Replace CSC 152 (Blum) 11

Paste the copied degree symbol into the Find what input, put nothing in the Replace with input and click Replace All CSC 152 (Blum) 12

Repeat with “ IN” being sure to include the space before. If the space is left behind Excel may not interpret the cell contents as a number CSC 152 (Blum) 13

One indicator of success is that Column C is now right justified – Excel’s standard for numbers CSC 152 (Blum) 14

Right click on Column C and choose Insert CSC 152 (Blum) 15

Highlight Column B, click on the Data tab and choose Text to Column CSC 152 (Blum) 16

Choose Delimited, click Next CSC 152 (Blum) 17

Change the Demiliter to Other, make it a slash (/). Click Next CSC 152 (Blum) 18

Choose General and click Finish. CSC 152 (Blum) 19

Repeat for Column G CSC 152 (Blum) 20

Highlight Column F, right click and choose Delete CSC 152 (Blum) 21

Replace the date in cell A 1 with 8/1/2018 CSC 152 (Blum) 22

In Cell A 2, enter the formula = A 1+1 CSC 152 (Blum) 23

Enter formula, highlight it, move into corner (get thin cross) and double click to copy formula down The ####### Pound signs (hash tags? ) arise because the cell is not wide enough to display the date. Place the mouse between A and B, when it becomes a double headed arrow. Either double click (auto spacing) or drag (user spacing). CSC 152 (Blum) 24

Highlight the first three columns and go to Insert/Line Chart/Lines with Markers CSC 152 (Blum) 25

Under Chart Tools/Design choose a Quick Layout (e. g. #10) To obtain features like titles and axis labels individually one goes to the Layout tab, but to obtain such features en masse one goes to the Design tab and uses the set Layouts found there. CSC 152 (Blum) 26

Edit the Chart Title and y-axis label. (I got the degree symbol by using Insert/Symbol). Then delete the x-axis label. CSC 152 (Blum) 27

Right click on a data point, and choose Select Data CSC 152 (Blum) 28

Choose Series 1 and click on Edit. CSC 152 (Blum) 29

Give the series a name and click OK. Repeat for Series 2. CSC 152 (Blum) 30

Right click on the vertical (High-Low) lines and choose Delete from the menu CSC 152 (Blum) 31

Right click on the dates on the x axis and choose Format Axis. CSC 152 (Blum) 32

Scroll down, expand Number. Change the Type of date. CSC 152 (Blum) 33

Next at top of panel choose Text Options on the Format Axis side panel. Then choose Textbox CSC 152 (Blum) 34

Change the Text direction CSC 152 (Blum) 35

Right click on the outer part of the Chart and choose Format Chart Area CSC 152 (Blum) 36

Choose Fill and use the Paint bucket dropdown to change the color CSC 152 (Blum) 37

Right click on the interior (plot area) and choose Format Plot Area CSC 152 (Blum) 38

Choose Fill and use the Paint bucket dropdown to change the color CSC 152 (Blum) 39

On the Home tab, highlight the last row of data and click on the Border control on the Home tab and choose Thick Bottom Border CSC 152 (Blum) 40

http: //www. ltcconline. net/greenl/courses/201/descstat/mean. htm The website above (and many others) gives some explanation of why the mean (our usual concept of “average”) can be thrown off by outliers. Were there any temperature outliers in August? CSC 152 (Blum) 41

In cell B 32 enter =AVERAGE( then drag along the column of data from B 1 to B 31). Then close the parentheses and click Enter. CSC 152 (Blum) 42

In cell B 33 enter the formula =MEDIAN(B 1: B 31) Be sure to choose the range B 1: B 31 and not B 1: B 32. It is a common mistake when you are using a range to calculate several different quantities to include your previous quantities in your range. Be careful. CSC 152 (Blum) 43

Highlight cell B 34 and click on the fx button next to the formula bar. Choose the Statistic category (or All) and scroll to MODE. SNGL. Then click OK. CSC 152 (Blum) 44

Next Enter the range B 1: B 31 in the Number Textbox. Then click OK. (You can also click on the right of the inpput a then drag over the range. ) CSC 152 (Blum) 45

Next we will turn to measures of “spread” in the data like the standard deviation CSC 152 (Blum) 46

In cell B 35 enter the formula =STDEV. S(B 1: B 31) CSC 152 (Blum) 47

Enter another version of standard deviation STDEV. P in the next cell CSC 152 (Blum) 48

Calculate the minimum of the distribution by entering the formula =MIN(B 1: B 31) CSC 152 (Blum) 49

Enter the text “Second smallest” into cell A 38, then go to Format/Format Cells CSC 152 (Blum) 50

Under the Alignment tab, check Wrap text CSC 152 (Blum) 51

In cell B 38 enter the formula =SMALL(B 1: B 31, 2) The second “argument” of the function determines which smallest value: 2 for second smallest, 3 for third smallest, etc. CSC 152 (Blum) 52

Calculate the maximum of the distribution in cell B 39 by entering the formula =MAX(B 1: B 31) CSC 152 (Blum) 53

Similar to the second smallest, determine the second largest with =LARGE(B 1: B 31, 2) CSC 152 (Blum) 54

Quartiles are an extension of the concept of median (which is the second quartile). The first and third quartile indicate the data at the 25% and 75% mark if the data were ordered (sorted). CSC 152 (Blum) 55

Determine the First Quartile by entering the formula =QUARTILE. INC(B 2: B 32, 1) CSC 152 (Blum) 56

Determine the Third Quartile by entering the formula =QUARTILE. INC(B 1: B 31, 3) CSC 152 (Blum) 57

http: //www. purplemath. com/modules/boxwhisk. htm The Quartiles including the zeroth (minimum) and fourth (maximum) go into making what is called the “box and whiskers” plot. CSC 152 (Blum) 58