Constructing the animated spreadsheet

• Prepare sheet 1 of an Excel workbook as shown in the figure below.  All numbers in the spreadsheet represent physical quantities in SI units.

  

  • Column B holds the parameters for wave 1.  Column C holds the parameters for wave 2.   The entries in cells B2  -B6, B11, and C2 - C6 are directly typed into these cells.  Cells B7, B8, C7, and B8 contain formulas.
  • The wave number k is k = 2π/λ, where λ is the wavelength of the wave.  Cell B7 therefore contains the formula =2*PI()/B3.
  • The angular frequency ω is given by ω = 2πf = 2πv/λ = vk.   We multiply ω by +1 for a wave traveling in the positive x-direction and by -1 for a wave traveling in the negative x-direction.  Cell B8 therefore contains the formula =+B4*B7*B6.
  • Copy cells B7 and B8 to cells C7 and C8.
  • Column D holds the position x.  Let x run from 0 to 20 in steps of 0.1.  Then the last entry will be in row 202.
  • Column E holds the displacement y₁ of wave 1 as a function of the position x at the time shown in cell B11.  In cell E2 type the formula =$B$2*SIN($B$7*D2-$B$8*$B$11+$B$5).  Copy this formula down to row 202.
  • Column F holds the displacement y₂ of wave 2 as a function of the position x at the time shown in cell B11.  In cell F2 type the formula =$C$2*SIN($C$7*D2-$C$8*$B$11+$C$5).  Copy this formula down to row 202.
  • Column G holds the vector sum of wave 1 and wave 2 as a function of the position x at the time shown in cell B11.  In cell G2 type the formula =E2+F2. Copy this formula down to row 202.
• Create a chart.  Highlight the data in columns D and E and insert a chart of type XY (Scatter) into sheet 1.  Pick the subtype scatter with data points connected by smoothed lines without markers. Do not include any titles and uncheck the gridlines and legend.
  • Click on the x-axis, choose format axis, scale, maximum 20.
  • Click on the y-axis, choose format axis, scale, minimum -2, maximum 2.
  • Your plot now shows the displacement y₁of wave 1 as a function of the position x at the time shown in cell B11.  To add the displacement y₂ of wave 2 as a function of the position x at the time shown in cell B11, highlight the data in column F, click edit, copy on the menu bar, click inside the graph, and click edit, paste on the menu bar.  Similarly you can add a plot of y₁+y₂ as a function of the position x at the time shown in cell B11 to the graph.  You can change the color and weight of the lines in your plot by clicking on a plot, and choosing format data series.
  • You can remove a plot from the graph by clicking on it and pressing delete.
• Animate your graph. Click the developer tab, and then click macros.  Choose a macro name, for example "wave", and click create.
  • A Visual Basic for Application (VBA) window will open. It will contain two entries.
    • Sub wave()
      End Sub

    Enter the following program.

    • Sub wave()
      Range("b11").Value = 0
      Do While Range("b11").Value < 10
      Range("b11").Value = Range("b11").Value + 0.02
      Worksheets(1).Calculate
      DoEvents
      Loop
      End Sub

    The program tells Excel to start with a value of 0 in cell B11 (the time) and increment this value in steps of 0.02.  After each step Excel must recalculate all formulas and update the graph.  Excel will keep on incrementing as long as the entry in cell B11 is less than 10.

  • Close the VBA window.
  • Click on an empty cell in your worksheet. On the menu bar click tools, macro, macros, run. Watch your animated graph.