ࡱ>    !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKRoot Entry@tL@Workbook mWksSSWorkBook=WksSSChart #,##0.0000000\)                + ) , *            "  #"""     "" @ "@@ "@ @ "@ "@  " "@"@       """!"@ @  @"@@"@" @   !@ @   @   "@  "@@ "@ @!@@!@! @  " "@ @ "@ @ " @  @  "@  !@ "@ @"@ @ "@ @ "@ @ "@@ " @  "@@ "@ @ @@"!@ @   @ @ # #"""@ @  @ @  " ! @ @  @ @  @ @  @  @  "@@"@" @  @ @  @ @ "!@ @ "@ @ "@ @ "@ @  @ @ `Sheet1     dMbP?_*+% |Page #&?'?(?)?"??M oU} } }  } } } } 4} } } } } } } }  } }  }   ,     \pMS Works Ba==i-818X@"1Arial1Arial1Arial1Arial1Arial1 Arial1 Arial1h Arial1x Arial1 Arial1 Arial1 Arial"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_) 0.00.0000.00000.000000.000000 0.00000000E+000.0E+00 0.000E+00 0.0000E+00 0.00000E+00 0.000000E+00 0.0000000E+001$#,##0.0_);($#,##0.0\)9$#,##0.000_);($#,##0.000\)=$#,##0.0000_);($#,##0.0000\)A$#,##0.00000_);($#,##0.00000\)E $#,##0.000000_);($#,##0.000000\)I"$#,##0.0000000_);($#,##0.0000000\) 0.0%0.000%0.0000%0.00000% 0.000000% 0.0000000%#,##0.0 #,##0.000 #,##0.0000 #,##0.00000 #,##0.000000 #,##0.0000000 d\-mmm\-yy mmmm d, yyyy m/d m/yy mmmm, yyyymmmm dd mmm mmmm dd dddd yy yyyy 00 000 000000000000000000000000000000#\ ?/2#\ ?/3#\ ?/4#\ ?/8#\ ?/10#\ ?/16#\ ?/32#\ ?/100=$#,##0.0_);[Red]\($#,##0.0\)E $#,##0.000_);[Red]\($#,##0.000\)I"$#,##0.0000_);[Red]\($#,##0.0000\)M$$#,##0.00000_);[Red]\($#,##0.00000\)Q&$#,##0.000000_);[Red]\($#,##0.000000\)U($#,##0.0000000_);[Red]\($#,##0.0000000\)9#,##0.0_);[Red]\(#,##0.0\)A#,##0.000_);[Red]\(#,##0.000\)E #,##0.0000_);[Red]\(#,##0.0000\)I"#,##0.00000_);[Red]\(#,##0.00000\)M$#,##0.000000_);[Red]\(#,##0.000000\)Q&#,##0.0000000_);[Red]\(#,##0.0000000\) #\ ??/???) #,##0_);( #,##0\)1 #,##0.0_);( #,##0.0\)5 #,##0.00_);( #,##0.00\)9 #,##0.000_);( #,##0.000\)= #,##0.0000_);( #,##0.0000\)A #,##0.00000_);( #,##0.00000\)E  #,##0.000000_);( #,##0.000000\)I" #,##0.0000000_);( #,##0.0000000\)5 #,##0_);[Red]\( #,##0\)= #,##0.0_);[Red]\( #,##0.0\)A #,##0.00_);[Red]\( #,##0.00\)E  #,##0.000_);[Red]\( #,##0.000\)I" #,##0.0000_);[Red]\( #,##0.0000\)M$ #,##0.00000_);[Red]\( #,##0.00000\)Q& #,##0.000000_);[Red]\( #,##0.000000\)U( #,##0.0000000_);[Red]\(?)6Clearing the Lunar Distance using Calculated AltitudeszqThis spreadsheet is designed to assist amateurs in learning about lunar distances purely for their own enjoyment.The author is neither a professional navigator nor a professional programmer, so please do not rely on this spreadsheet for actual navigation.The spreadsheet refers to the article "Lunar Distances Explained along with a Starter Kit for the Shore Bound Navigator" to illustrate various points.ypEnjoy your lunars, explore the calculations in the cells of this spreadsheet, let me know if you find any errors54,- Arthur Pearson (arthurpearson@hotmail.com)q/& Information to be entered by the userr/& Results calculated by the spreadsheet )Basic Information 7Notes: =Date:~ ;_@ < < B2 4)Date of observation in format DD/MM/YYYY. > 0Degrees 0Minutes 0 N or S  C >Latitude~ -2~ -:@ -N C; 42Latitude of observation. Note whether "N" or "S".>0Degrees0Minutes0 E or W C> Longitude~ ,~ -@ +WC;42Longitude of observation. Note whether "E" or "W".>*MinutesC?Index Correction@~ s[@D4|This is the CORRECTION, not the error, for the sextant. If the index error is "on the arc", the index correction is negative89 Almanac Data8HComparing Body GSun<<B4Type "Sun" or "Venus" to invoke parallax corrections unique to those bodies. All other entries invoke parallax correction of zero, which works for any star or outer planet. It only matters that you spell "Sun" or "Venus" correctly when you IEDegrees0Minutes3CIGHA Hour Before~ .b~ - @C4The GHA for the hour BEFORE the GMT of the observation taken from the hourly values on the main pages of the almanac. If comparing body is the sun or a planet, use the GHA of that body. For stars, enter GHA of Aries here and SHA of the stIEDegrees0MinutesCIGHA Hour After~ .~ -0@C4The GHA for the hour AFTER the GMT of the observation. The calculations will interpolate between the hourly values to get GHA for the moment of observation for calculating the altitude.IEDegrees0Minutes0 N or S C IDeclination Hour Before~ .V~ -@ -SC4As above, declination for the body in question comes from the hourly values on the main pages of the almanac. Stars have one Dec. value listed for each 3 day period. Note whether "N" or "S".IFDegrees0Minutes0 N or S CIDeclination Hour After~ .V~ -ֵ@ -SCE4<From the main pages of the almanac. Note whether "N" or "S".IFDegrees0MinutesCISHA if body is a star~ 2~ -C4If the comparing body is a star, you need Sidereal Hour Angle (SHA). Stars have one SHA value listed for each 3 day period. Must type a star name as "Comparing Body" above for SHA to be used. If you type "Sun" or "Venus", SHA will not be u !"#$%&'()*+,-./0123456789:;<=>? I 1Minutes C%!JSemi diameter if body is sun!@~ !Ax@!@!D!4One value for "SD" is listed for each 3 day period at the bottom of the sun's column of data on the main pages of the Almanac. SD=0 for all other bodies."8#HBody: #MMoon#<#<#B,#4#These data are always for the moon.$I$KDegrees$KMinutes$C%IGHA Hour Before~ %.~ %.h@%C9%40The GHA for the hour BEFORE the GMT observation.&I&EDegrees&EMinutes&C'IGHA Hour After~ '.2~ '.@'C'4The GHA for the hour AFTER the GMT of observation. The calculations will interpolate between the hourly values to get GHA for the moment of observation for calculating the altitude.(I(EDegrees(EMinutes(C )IDeclination Hour Before~ ).~ ).ؓ@ )-N)CE)4<From the main pages of the almanac. Note whether "N" or "S".*I*EDegrees*EMinutes*C+IDeclination Hour After~ +2~ +.@ +-N+CE+4<From the main pages of the almanac. Note whether "N" or "S".,I,EMinutes,C!-JHorizontal Parallax Moon-@~ -N,@-@-D-4"HP" for the moon is listed for each hour of the day. It is at the far right side of the moon's column of data in the main pages..8/:The Lunar Distance081=1PHours1PMinutes1QSeconds1B2> GMT per watch~ 2.J~ 2/~ 2.2C24This should be from a watch that is adjusted to show GMT. We compare this "actual" GMT to what we derive from the lunar to see how close we came.3>3EDegrees3EMinutes3O Moon's limb3C 4?Ds~ 4Nr~ 4NL@ 4NNear4D44This is the sextant distance we observed before any corrections. Type "Near" or "Far" to indicate the limb of the moon used relative to the comparing body. All corrections are made by the spreadsheet.6)Results8[8XHours8YMinutes8ZSeconds8B9>GMT per Lunar Distance9S2@D29RD@D9TPp. E@D9C94vThis is GMT as calculated from the lunar distance. Calculations are laid out below and can be followed by the curious.:>:0Minutes:3Seconds:C:5:;>DYour lunar appears to be:;VDFast;T?D;W@Mp. K@D;C;4{If you set your watch to GMT from a reliable source, any error slow or fast is a result of error in measuring the distance.<><0D Minutes<C=?DError in observation:=@=\yٺ?D=@=D=5Assuming your watch had accurate GMT, this is shows the error in measured distance that would cause the lunar and the watch to be different.=U?LCongratulations, you are now a lunarian in the tradition of Cook and Slocum!DEFH,IJKLMNOPQRSTUVWXYZ[\]^,_`abcdD)The Calculations_EVFor the curious, the calculations behind the results are laid out and annotated below.FThe annotation occasionally refers to the article "Lunar Distances Explained along with a Starter Kit for the Shore Bound Navigator"EH]<Collecting what we know and expressing it in decimal degreesfI]All subsequent angles and altitudes are expressed in decimal degrees (e.g.. 20 30' = 20.5)JDegrees KLatitude of Observation-K^&X)@D D <DOKFBy convention, North latitude is positive, South latitude is negative.K!!LLongitude of Observation-L^:mNDD<DOLFBy convention, East longitude is positive, West longitude is negative.LM NBody NMoonO SHA if a Star6Ot D DD<B OSN/AHO?Almanac data converted to decimal degrees. Only stars have SHA.PGHA Hour BeforehPtK~V@RDD<DOh DD<DODD<DOhB&Pt7i^v@D%D%<+P" " "PPQGHA Hour AfterhQt`,ŒY@RDD<DOh DD<DODD<DOhB&Qt6i)@D'D'<+Q" " "QQ RDeclination Hour Before-Rti65DD<D-RtY%@D)D)<DTRKBy convention, North declination is positive, South declination is negativeRRSDeclination Hour After-StN贁5DD<D-St7i@D+D+<D+S" " "SSTHorizontal ParallaxTua2U0*c? D<Tt鴁N? D-<|TsHP for the Sun is fixed, HP Venus is in a lookup table, HP for moon we entered above, HP for all other bodies is 0.TTT U Semi diameterUUVVWInterpolation factor+WtZH7?D2<D2WtZH7?DWsWjThis factor is the % of 1 hour that has transpired between the hour before and the time of the observationWWXXXX#YGHA at time of ObservationYtaDF8tX@DQDP DPDQDPDWDPhDQDPDWh DPhDQDPDWDPhDQDPDWhBBYtb@DQDP DPDQDPDWDPhDQDPDWh DPhDQDPDWDPhDQDPDWhBBdY[GHA of the observation obtained by interpolating between the hour before and the hour after$ZDec. at time of Observation.Zt(ӣ35DRDSDRDW.Zts(h@DRDSDRDWlZcDeclination of the observation obtained by interpolating between the hour before and the hour after#[LHA at time of Observationg[tODB@QDYDLh DYDLhDYDL DYDLhDYDLBBg[tC(f!s@QDYDLh DYDLhDYDL DYDLhDYDLBB[LHA of the body which is (GHA + Longitude when west longitude is negative and east longitude is negative). 360 added or subtracted to bound result from 0-360.]]-^]$Calculating the "Observed" Altitudes^^_Recall that to clear the distance, we need Apparent Altitudes (corrected for index error and dip) and Observed Altitudes (corrected for parallax and refraction)__`Normally we would measure the altitudes, correct to get Apparent Altitude, then correct again to get Observed Altitudes (see section on Sextant Corrections in article).``ualFor the backyard lunar, we will calculate the "Observed" Altitude based on our known location and known GMT.aabbcWe know:ccd" 90-Latitude =d%贁NCS@ ZDKdcoLatefghijklmnopqr,stuvwxyz{|}~,e"90-Declination Body =e% [@ ZDZecoDec~euRefer to Figure 3 of article for a graphic representation of the navigational triangle and our solution for altitude.f" LHA Body =f%ODB@D[ fLHAgWe can calculate:g$!h_Body's Observed AltitudehvN ǽC@rZDeRFߑ?ADdRFߑ?ADeRFߑ?ADdRFߑ?ADfRFߑ?AAcRFߑ? h8SoiijWe know:j jj5jk" 90-Latitude =k贁NCS@ ZDKkcoLatk5kl"90-Declination Moon =lĘ~u})U@ ZDZlcoDec~luRefer to Figure 3 of article for a graphic representation of the navigational triangle and our solution for altitude.l5lm" LHA Moon =mC(f!s@D[ mLHAm5mnWe can calculate:nnn5n!o_Moon's Observed AltitudeovO'B@rZDlRFߑ?ADkRFߑ?ADlRFߑ?ADkRFߑ?ADmRFߑ?AAcRFߑ? o8Moo5opp5pqqq+r]"Calculating the apparent altitudesrr5rs|We will now calculate parallax and refraction "in reverse", apply them to the Observed Altitude to derive Apparent Altitude.ss5stt5t!u"Body's Observed Altitudeu%N ǽC@Dh u#Sou5uv"Parallaxv%]F^?vD -C6j?DKRFߑ?ADuRFߑ?An DDuRFߑ?AARFߑ?B v_- v$Pv w"So'!w%kC@ DuDv w_= w%So'wx" Refractionax%WI[?KRQ?Z#J %?Dw$@Dwq= ףp@RFߑ?A< x_+ x$Rx!y_Body's Apparent Altitude!yvtD@ DwDx y_= ybSayzzuzlThese calculations simply reverse the corrections described in the article's section on sextant corrections.zz !{"Moon's Observed Altitude{%O'B@Do{ {#Mo|"Parallax||%Y>?f-C6j?DKRFߑ?AD{RFߑ?An D-D{RFߑ?AARFߑ? |_- |#P }"Mo'!}%|8A@ D{D| }_= }#Mo'~" Refractiona~%%?KRQ?Z#J %?D}$@D}q= ףp@RFߑ?A< ~_+ ~#R!_Moon's Apparent Altitude!vV1wA@ D}D~ _= bMa`5]Clearing the Distance `5Refer to Figures 5 and 6 and associated text of the article for an explanation and graphic representation of clearing the distance. `5 `5u,"Sextant Distance&%Œ_,!W@D4D4<` DsF=This is the distance we entered above before any corrections."Index Correction%ƒ_,Œ D< a+/- IC,#The index CORRECTION entered above."Moon's semi diameterW%4?Aq= ףp?DTDTRFߑ?ADRFߑ?AD a+ SDmThe moon's semi diameter is added when measuring to the "Near" limb, subtracted when measured to the "Far" limb. It is calculated from the Sa, HP and Latitude.&"Body's semi diameter (if sun)/%c/b?D D!<B a+/- SDsThe sun is the only comparing body that has a semi diameter. It will always be added as the sun is always measured to the "Near" limb of the moon._Apparent Distance-vfIAW@DDDD a= 8DaWe know: "Apparent DistancecfIAW@D Da$"90-Body's Apparent Altitudecl@ H@ ZDy coSa!Sa was calculated above.$"90-Moon's Apparent Altitudect(3K@ ZD coMa!Ma was calculated above. &We can calculate:_Difference in Azimuthsvq٨`@nDRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?AAcRFߑ? 8dZnWe know:$"90-Body's Observed Altitudec8BI@ ZDh coSo!So was calculated above.$"90-Moon's Observed Altitudec_'J@ ZDo coMo!Mo was calculated above."Difference in Azimuthscq٨`@D dZn"dZn was calculated above.We can calculate: _Observed Distancev؃J{W@nDRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?AAcRFߑ? 8DoVoila! The fully cleared distance. We will use this to compare to distances calculated for the hour before and after to interpolate for GMT.D];Calculating the Distance for the Hour Before and Hour AfterRefer to Figure 7 and associated text of the article for an explanation and graphic representation of calculating these distances.$We know for the Hour Before"90-Declination Moon@ t2U@ ZDRcoDec M"90-Declination Bodyt@ [@ ZDRcoDec S"Hour Angle Moon - Body;q= ףp@%DPDP DPDPDPDPB HAWe can calculate_Dc for Hour Beforew"W@nDRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?AAcRFߑ? 8Dc1#We know for the Hour After"90-Declination Moonb/$U@ ZDScoDec M"90-Declination Body:m[@ ZDScoDec S ,,"Hour Angle Moon - Body;(X%V@%DQDQ DQDQDQDQB HA We can calculate_Dc for Hour Afterw c%W@nDRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?ADRFߑ?AAcRFߑ? 8Dc2   A]8Interpolating Between Calculated Distances to Derive GMT Now we interpolate between the distances calculated for the Hour Before and After to determine GMT at the time of our Observed Distance. !' PGWe know all of the following except GMT of the Observed Lunar Distance: fDistancefGMT Hr"Dc1 (for Hour Before)h"W@Di2@D2 "Observed Lunar Distanceh؃J{W@D d?"Dc2 (for Hour After)h c%W@Di3@ D(By interpolation, we calculate:fDistancefGMT Hr"Dc1 (for Hour Before)h"W@Dj2@D "Observed Lunar Distanceh؃J{W@D5eJ->2@DDDDDC4:This is GMT per Lunar Distance expressed in decimal hours."Dc2 (for Hour After)h c%W@Dj3@D0Hours0MinutesgSeconds"GMT per Watch was:L2@D2LC@D2LG@D224)As entered at the top of the spreadsheet."GMT per Lunar is:T2@D*TD@DD<B2TPp. E@DD<D<P4GThis is GMT per Lunar Distance converted to hours, minutes and seconds.0Minutes"Error in Minutes:E^L?/DDDD<DD<N4EThis is the apparent error of the watch expressed in decimal minutes.0MinutesgSeconds""Your lunar appears to be:9S#D FastSlowBFast"T? DAB)T@Mp. K@DAD<_4VThis is the apparent error of the watch converted to minutes and seconds fast or slow.0Minutes"Error in observation:KUyٺ?5D<DD DDDDB<5Assuming your watch had accurate GMT, this is shows the error in measured distance that would cause the lunar and the watch to be different.4]+Miscellaneous factors used in calculations:[Sign of Latitude1<?D N BB kSign of Longitude1DW BC6!kBody (Sun, Venus, other)I?3DSun DVenus BBC&41 = Sun, 2 = Venus, 0 = other#kSign of Body Declination 11DN BC4#kSign of Body Declination 21DN BC4#kSign of Moon Declination 11?D)N BC4#kSign of Moon Declination 21?D+N BC4kNear/Far limb of moon7?!D4Near BC4kSun HP#;On? a2U0*c?< Cmin4From Nautical AlmanackVenus HP+?D %Bf CminE4<From look up table below which is based on Nautical Almanac. kUsed HP=;On?'D $D $BB Cminn4eNote that HP is expressed in minutes here. The formula for parallax used minutes rather than radians.lRadians per degreemRFߑ?D4Spreadsheet trigonomtry formulas require angles to be expressed in radians rather than degrees. This conversion factor is embedded in those formulas to 16 decimal places.4 %Lookup table of HP for Venus4  nDateBMinutes4~ o^@~ C$@M4DFrom the Nautical Almanac, formatted to a spreadsheet look up table. ~ oI@~ C$@~ oI@~ C4@! ~ oO@~ C4@ ~ oO@~ C>@ !~ oR@~ C>@ (!~ oR@~ CD@ ( !~ oT@~ CD@ ~ oT@~ C?~ oY@~ C?~ oY@~ CD@~ o[@~ CD@~ o[@~ C>@~ o`^@~ C>@~ p^@~ D4@> ) Arial