Using a Spreadsheet to Calculate Artificial Gravity

Introduction:

Consider an object travelling in a circular path with radius r.  The object has a velocity v, and would travel in the straight line path shown by v, if it was not being accelerated inwards.   The object travels in a circular path – an orbit – because it is being constantly accelerated inwards with an acceleration a.  The mathematical formula for this centripetal acceleration is:

[1]

We can change the linear velocity into a rotational velocity, since , where is the rotational velocity in radians per second.  Substituting this into [1] for v gives the formula for acceleration as:

[2]

The number of revolutions the object makes around its orbit every second is the frequency, f.  Since there are 2 radians in a circle:

[3]

If we want the frequency in revolutions per minute we need to multiply f (per second) by 60 (seconds per minute) which gives us

[4]

which if solved for is

[5]

Substituting equation [5] into equation [2] for gives:

[6]

Since we want to generate an acceleration equal to earth's gravity, we can replace a by 9.8 m/s:

[7]

and then rearrange the formula to solve for r:

[8]

In  equation [8], f is the rotational frequency of the spacecraft in revolutions per minute, while r is the radius of the orbit in m.  This is the equation that we will use to create the spreadsheet.

Procedure:

1. Open this Excel template. Note: instructions given here are for Excel 97.   Other spreadsheets will use similar procedures.

2. Fill the column for f with frequencies from 1 to 30 rpm, at selected intervals, as shown here:
3. In cell A3, enter the formula for the spreadsheet that will let you calculate the radius.  Remember that the value for f is actually in cell B3.  Hint: the value of is a built in function in the spreadsheet, called PI().  See your spreadsheet's help file for how to use it in a calculation.  If you enter the correct formula into the spreadsheet you should get a value of about 894 m in cell A3.

4. Copy the formula from cell A3 into cells A4..A11

5. Select cells A3 through B11 with your mouse.  Then click the chart wizard icon on the spreadsheet.  Complete the graph as follows:

• select chart type: XY (Scatter) with chart sub-type data points only (no lines) then press [Next]
• the correct series will have already been selected if you highlighted the proper cells in procedure step 5.  Press [Next]
• enter appropriate titles for the graph, and the x and y axes
• click the gridlines tab and turn on the major gridlines on the x axis
• click the legend tab and turn off show legend, then press [Next]
• place the chart as a new sheet and press [Finish]
• to draw the line on the graph, right click on one of the data points, and select Add trendline.  Choose the power option.  Explain why this is the best choice, by looking back at the equation that is being plotted in equation [8].

6. Complete and either save or print the graph as instructed by your teacher.