Analyzing and Viewing Weather Statistics in Excel

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

2. 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)

3. Click on the Month tab CSC 152 (Blum)

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

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

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

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

8. Result of the paste. CSC 152 (Blum)

9. 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)

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

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

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

13. 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)

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

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

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

17. Choose Delimited, click Next CSC 152 (Blum)

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

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

20. Repeat for Column G CSC 152 (Blum)

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

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

23. In Cell A2, enter the formula = A1+1 CSC 152 (Blum)

24. 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)

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

26. 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)

27. 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)

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

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

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

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

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

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

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

35. Change the Text direction CSC 152 (Blum)

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

37. Choose Fill and use the Paint bucket drop-down to change the color CSC 152 (Blum)

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

39. Choose Fill and use the Paint bucket drop-down to change the color CSC 152 (Blum)

40. 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)

41. http://www.ltcconline.net/greenl/courses/201/descstat/mean.htmhttp://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)

42. In cell B32 enter =AVERAGE( then drag along the column of data from B1 to B31) . Then close the parentheses and click Enter. CSC 152 (Blum)

43. In cell B33 enter the formula =MEDIAN(B1:B31) Be sure to choose the range B1:B31 and not B1:B32. 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)

44. Highlight cell B34 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)

45. Next Enter the range B1:B31 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)

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

47. In cell B35 enter the formula =STDEV.S(B1:B31) CSC 152 (Blum)

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

49. Calculate the minimum of the distribution by entering the formula =MIN(B1:B31) CSC 152 (Blum)

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