Faubion Middle School's Spread Sheet Calculations

The table below was created using the Excel Spreadsheet. Below the table is a list by column of explanations of the mathematics used to produce the specific column's results. The Excel formula is also given.

Explanation of the Excel Spreadsheet Formula
Column by Column

by
Kenneth Cole, Mathematics Teacher
Faubion Middle School
March 31, 1997

Note: In the formula below, the green highlighted variables labeled as column 1, column 7, etc., in Excel as in most spreadsheets are replaced by the actual cell name, i.e., use for example, column 7. If the column of the cell was labeled "G" and the row of the cell is labeled "6" instead of column7, the formula would have an entry of "G6".
So, the formula in Column 7 below which is written, =90-ATAN[(column 6)/100]*57.3*SIGN(column 5), in Excel would actually be written with the cell names for row 4, =90-ATAN[(F4)/100]*57.3*SIGN(E4).
One further note: One modification which was made in the formulae written below is the use of hierarchical grouping symbols which Excel does not use. Thus, the formula given in the preceeding paragraph whould be written: =90-ATAN((F4)/100)*57.3*SIGN(E4) in Excel. In other words, the {x[y(x+y)]} grouping symbols are written (x(y(x+y))) in Excel.

Column 1: Site Name: School Name

Column 2: City

Column 3: State

Column 4: Country

Column 5: Latitude—positive value indicates northern latitude--negative value indicates southern latitude

Column 6: Length of shadow of a meter stick at local noon time

Column 7: The angle of the Sun at noon—This is found by dividing the height of the stick (100 centimeters) by the length of the shadow in centimeters, which yields the tangent ratio of the angle of the shadow. Then to find the measure of the angle of the shadow in radians, you take the arc tangent of that ratio. Then, to convert from radians to degrees, you multiply by 57.3, which is the number of degrees in a radian. Next, to find the angle of the Sun, you subtract your answer from 90°. The last thing we do is multiply by the sign of the latitude to insure that the sites from the southern latitudes show a opposite direction from the sites in the northern latitudes for distance purposes later.

The Excel formula which we use is:
=90-ATAN[(column 6)/100]*57.3*SIGN(column 5)

Column 8: The North-South Distance—To understand what we are doing here, just think about why we take our readings at our local noon time. Imagine that we are lining up all of our schools at the same place on the globe, longitudinally, say at the Prime Meridian. The only distance between the schools would be the north-south distance.
Which school is the starting place? We have to choose. Since Lindfield Primary had a very accurate measure of the angle of the Sun, we choose to start there. This is somewhat unfortunate for Wanniassa Hills Primary because Lindfield is very close in distance, north to south that is. This magnifies any error which both sites make. That is why the deviation at Waniassa Hills' site seems so large compared to the other sites. All it really means is that we are looking at a much smaller piece which makes our measurements seem less accurate.
It might compare with trying to find the circumference of a basketball by bending a one centimeter long wire and sketching the circumference of the basketball from that arc. Then bending a 20 centimeter long wire and using that arc to sketch the shape of the circle for the circumference of the ball.
Now to find the circumference, we need to know how far in degrees our sites are from Lindfield. We find that distance by subtracting current site's latitude, in degrees, from -33.8°, which is the latitude of Lindfield. To find the distance regardless of the direction, we use the absolute value of that number of difference in degrees. To convert the distance from degrees to kilometers, we then multiply by 111.133 the number of kilometers in a degree on the north-south circumference of the Earth. You can find this number by dividing the actual circumference, 40008 km by 360.

The Excel formula which we use is:
=ABS(-33.8-column 5) *111.133

Column 9: Circumference Estimate—Once you know the distance between the two sites and the difference between the angle of the Sun at the two sites, you simply divide the distance by the angular difference which gives you the distance of a one degree arc. You might remember that the degree measure of an arc is the same as the degree of angle which defines it. Therefore dividing the number by itself yields one degree. Now to find the circumference estimate, multiply that one degree length by 360.

The Excel formula which we use is:
=ABS[(column 8*360)/(122.04893-column 7)]

Column 10: The Percent of Deviation from the Actual Circumference—To find this number we subtract the actual circumference from our estimate in column 9, divide the answer by the actual circumference, 40008 km. To convert this ratio to a percent, we multiply by 100.

The Excel formula which we use is:
=[(column 9–40008)/40008]*100

[FMS Noon Page] [Noon Project Schools]
[FMS Noon Calculations] [FMS Home Page] [FMS Sun Poetry]

This page hosted by Get your own Free Home Page

Last Updated on 4/9/97
By Kenneth Cole
Email: kennethcole@home.com