Home / Expert Answers / Computer Science / 1-open-the-file-sealevel-datagmsl-in-excel-the-dataset-is-contained-in-two-columns-the-first-colu-pa784

# (Solved): 1. Open the file sealevel_dataGMSL in excel.The dataset is contained in two columns. The first colu ...

1. Open the file sealevel_dataGMSL in excel.The dataset is contained in two columns. The first column contains date information. The first four-digit number is the year. The following four decimal places represent a percentage of the year that has passed in days, falling approximately on the middle of each month.1880.0417 = Jan 1880 (365 days x 0.0417 = 15.22 days = January 15)1880.1250 = Feb 1880 (365 days x 0.1250 = 45.63 days = February 14).1880.9583 = Dec 1880 (365 days x 0.9583 = 349.78 days = December 15)1897.625 = Aug 1897 (365 days x 0.625 = 228.125 days = August 16)The second column is the Global Mean Sea Level (GMSL) in millimeters, compared with the average level in 1990. 2. Create a new column showing the month and the year in values that are easier to interpret: Create a new column to the right of GMSL, and name it Year. Use the INT( ) function to put the year portion of the Time column in your new column. To do this, type in the first cell under the header of the new column, and then double click the bottom right corner of the cell to apply the function to all of the cells in that column. Create a new column to the right for the fraction of the year. Title this column Fraction. To do this, insert a formula in the cell below the header in the new column which subtracts Year from Time: then apply the function to all of the cells in that column. Create a new column to the right called days which will convert the Fraction column to the number of days that have passed that year. To do this, Write a formula in the new column that multiplies the value in Fraction by 365. Make sure that the value in the days column now matches the example values shown in step 1. Reformat this column as Numbers, and reduce the number of decimal points shown until only the whole number is shown. The days column shows what is referred to as the Julian day. The chart on the following page will allow you to match Julian Date to the Day/Month format we are more used to. Look at the chart and make sure you understand which date each number in the days column refers to. Answer the questions below the chart. Julian Date Conversion Table What month and day does the number 15 represent?______________________ What month and day does the number 46 represent?______________________3. How many years of data are represented in this dataset? ____________________There are several ways to find out. One way is to use the data filter. Go to the Data tab in the header, click Advanced, then select copy to another location and Unique records only. 4. How many GMSL values in the dataset are negative? _________________Use the COUNTIF function on your new worksheet to find out. 5. Graph the data from a single year (pick any year). Create a scatter chart. Use the Days column for the x axis, and GMSL for the y axis. Make sure to title the chart with the year. Add a linear trendline to the chart. Do the values change over the course of the year? Repeat for two other random years. Fill out the table below:

We have an Answer from Expert