Center Home -> Content Areas Home -> Math Home -> Project Activities -> Excel Activities ->

Sunspots and Geomagnetic Disturbances

 Activity Description Activity Guide Background

Part 1: Analyzing Trends in Geomagnetic Activity

• Open the Excel workbook containing the sunspot data. Import the AA index data from the internet into Excel.

 To import the AA index data into Excel: Go to the following site: ftp://ftp.ngdc.noaa.gov/STP/SOLAR_DATA/ RELATED_INDICES/AA_INDEX Open the file AA_Year Select all the data and Copy to the clipboard. Open the Excel workbook you have the sunspot data in. On sheet2, select columns A and Paste the data. You will need to insert a row at the top of the sheet in order to label the columns YEAR and AA INDEX
• Look at the AA Index data in tabular form. Do you notice any patterns?

• Create a connected scatterplot of the AA Index data from 1900-1997.

• Describe the geomagnetic activity over this extended period of time.  Do you notice any patterns in the graph?

• When was the largest peak?  When was the smallest peak?

• How many years elapse between low points?  The graph of the AA index shows a cycle of highs and lows.  To estimate the cycle’s period, we can calculate the average number of years between successive low points.  Calculate this average over at least 5 successive low points.

• Based on the pattern observed in the data, in what year should the next high peak in geomagnetic activity occur?  When should the next low occur?

• How does the period of the sunspot data (calculated in the Exploring Sunspots activity) compare to that of the AA index data?

Part 2: Comparing Sunspots and Geomagnetic Data

On sheet 3 of your Excel workbook, copy and paste the columns containing the years 1900-1997, sunspot numbers, and AA index.  Put the years in column A, sunspot numbers in column B, and AA indices in column C.

• Create a combination graph of both data sets against years to further explore the relationship.

 To create a combination graph of two data sets: Select the columns containing both data sets and the column containing the independent variable data (if desired). Click on the ChartWizard then click in the spreadsheet at the point where you want your graph to appear.  The first pop-up window shows all the different types of graphs that Excel can create.  Choose the Combination icon and click on Next. The next window asks you to further decide how you want the graph to look.  Choose option 2 to display one data series in a bar graph and one as a line.  Notice that this graph has a primary and secondary y-axis, one for each data series.  Click on Next. Step four in the process displays a picture of your graph.  The Data Series selection should be columns since your data is entered in columns.  If you have a column of data selected as the independent variable, the first column should be used as the X data.  Once you are satisfied with how the graph looks, click on Next to proceed to the final step. In the final step, you should title the graph and click on Finish.
• Format the graph so that the sunspot numbers are in an area plot instead of a column graph. Also change the scale on the secondary axis so that the AA Index data ranges from 0 to 60.
 To change the style of plot in an existing graph: Double click on the graph for editing. Under the Format menu, choose the group you wish to change (in this case, we want to change the Column Group) When the pop-up window appears, click on the Chart Type... button. Select the icon representing the type of chart you want. (For this graph choose the area plot.)
• Do the peaks and lows occur simultaneously?  close together? or perhaps opposite (one peaks while the other is at a low)?  Discuss possible reasons for any trends you observe.

• Discuss the geomagnetic disturbances during the declining phase of a solar cycle.  What trends do you notice?  For a further discussion of this, go to the following web page:

http://www.ips.oz.au/papers/richard/disturbance_occ.html

• Describe and interpret what you believe is the relationship between sunspots and geomagnetic disturbances.

Extensions:

Graph of AA index and Sunspot Numbers
ftp://ftp.ngdc.noaa.gov/STP/GEOMAGNETIC_DATA/AASTAR/aasspot.gif
(** Challenge:  Create a graph in Excel that looks exactly like this one.)

• Can you think of any other phenomenon that might be related to solar activity?  The home page of the NGDC (http://www.ngdc.noaa.gov) provides links to lots of scientific data.  Conjecture a possible phenomenon and its relationship to sunspot numbers.  Find the data of interest, import it into a spreadsheet and compare it with the sunspot data.

Back to Project Activities | Back to Math Homepage

Send questions or comments here.