NavList:
A Community Devoted to the Preservation and Practice of Celestial Navigation and Other Methods of Traditional Wayfinding
Re: Declination worksheet
From: Dan Allen
Date: 2004 May 19, 17:04 -0700
"3.14159265358979/180"
I hope no one will be offended if I point out a little spreadsheet trick. If you already know this trick, you're bound to say "oh come on... everybody knows that!". But a great many people don't, and if you're one of those who doesn't know the trick, you'll probably say "wow... I wish I had learned that earlier!".
So here's the trick: when you're doing trigonometry in a spreadsheet, pick any convenient cell, A1 for example, and enter 57.29578 [or enter the formula =180/PI()]. Then elsewhere in your spreadsheet, you use $A$1 in your formulas. For example, if you have a column of degrees in cells B1 through B36 and you want their sines in column C, you type the formula "=SIN(B1/$A$1)" in C1 and then copy that formula down. When you look at the copied formula in cell C36, you will find that it reads "=SIN(B36/$A$1)" which is just what we want. The "$" symbols make this an absolute cell reference so that you don't need to enter it by hand in every formula. It will save you a fairly significant amount of typing and it makes the spreadsheet formulas a bit easier to read.
Absolute cell referencing is one of the most important spreadsheet tricks, but it's one of the easiest to miss when you're learning. There are obviously lots of other uses for it besides this case of converting angles in degrees to "radians". Absolute referencing can also cover just a row or column instead of one single cell (e.g. A$1 or $A1). Try it to see how it works.
Frank R
[ ] Mystic, Connecticut
[X] Chicago, Illinois
From: Dan Allen
Date: 2004 May 19, 17:04 -0700
Absolute cell
references are indeed very handy, but I just wanted to keep it
simple. Frank's suggestions are good, but I wouldn't normally do any of
this stuff in a spreadsheet: I like writing things in a programming language (C,
Awk, Perl are my favorites) but I thought that a spreadsheet would be more
universal.
Frank's other point about accuracy is more important: the sun's
declination if graphed is not actually a sine curve, just close to
one.
Dan
-----Original Message-----
From: Navigation Mailing List [mailto:NAVIGATION-L@LISTSERV.WEBKAHUNA.COM]On Behalf Of Frank Reed
Sent: Wednesday, May 19, 2004 3:59 PM
To: NAVIGATION-L@LISTSERV.WEBKAHUNA.COM
Subject: Re: Declination worksheet
Dan A wrote in some spreadsheet
formulas:From: Navigation Mailing List [mailto:NAVIGATION-L@LISTSERV.WEBKAHUNA.COM]On Behalf Of Frank Reed
Sent: Wednesday, May 19, 2004 3:59 PM
To: NAVIGATION-L@LISTSERV.WEBKAHUNA.COM
Subject: Re: Declination worksheet
"3.14159265358979/180"
I hope no one will be offended if I point out a little spreadsheet trick. If you already know this trick, you're bound to say "oh come on... everybody knows that!". But a great many people don't, and if you're one of those who doesn't know the trick, you'll probably say "wow... I wish I had learned that earlier!".
So here's the trick: when you're doing trigonometry in a spreadsheet, pick any convenient cell, A1 for example, and enter 57.29578 [or enter the formula =180/PI()]. Then elsewhere in your spreadsheet, you use $A$1 in your formulas. For example, if you have a column of degrees in cells B1 through B36 and you want their sines in column C, you type the formula "=SIN(B1/$A$1)" in C1 and then copy that formula down. When you look at the copied formula in cell C36, you will find that it reads "=SIN(B36/$A$1)" which is just what we want. The "$" symbols make this an absolute cell reference so that you don't need to enter it by hand in every formula. It will save you a fairly significant amount of typing and it makes the spreadsheet formulas a bit easier to read.
Absolute cell referencing is one of the most important spreadsheet tricks, but it's one of the easiest to miss when you're learning. There are obviously lots of other uses for it besides this case of converting angles in degrees to "radians". Absolute referencing can also cover just a row or column instead of one single cell (e.g. A$1 or $A1). Try it to see how it works.
Frank R
[ ] Mystic, Connecticut
[X] Chicago, Illinois