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.
Last modified on July 27, 2001.