Welcome to the NavList Message Boards.


A Community Devoted to the Preservation and Practice of Celestial Navigation and Other Methods of Traditional Wayfinding

Compose Your Message

Add Images & Files
    Re: Navigation spreadsheet lessons
    From: Frank Reed
    Date: 2009 Jan 12, 20:06 -0800

    So let's get started.
    You've gone to docs.google.com and opened a new spreadsheet. Your browser now 
    displays a page titled "Unsaved spreadsheet". You see a series of menus: 
    File, Edit, View, etc. Below that there is a toolbar with a print button, 
    some edit controls, formatting options, etc. And then, of course, we see the 
    spreadsheet itself which consists of a table of lettered columns (A,B,C,...) 
    and numbered rows (1,2,3,...). You can refer to any cell in the table by 
    letter and number. So the second cell from the left, third row down, is cell 
    B3. And I should note here that you will see almost exactly the same layout 
    of menus, toolbars, and spreadsheet cells in any modern spreadsheet package. 
    If you open up OpenOffice (I use v.3), you will see two more toolbars with 
    more special functions and formatting options, but otherwise it's just like 
    the Google Docs spreadsheet.
    The spreadsheet in Google Docs opens up by default with a dividing bar in a 
    location that may be confusing for beginners. So first things first, move it. 
    It's a grey, horizontal bar that lies between rows 1 and 2 by default. Go to 
    the far left end where you see diagonal lines in a small rectangle between 
    the labels for rows 1 and 2. Your mouse will turn into a four-directional 
    drag cursor. Click and drag that UP. The bar will now be left above row 1 
    --out of the way for now.
    Let's jump right in and do a simple trig calculation. Go to cell A1 and type 
    in 45 (as in 45 degrees). Most spreadsheets, like most other computer 
    software, assumes that angles will be converted to pure angles, also known as 
    "radians", before being passed to any trigonometric functions. Modern 
    spreadsheets include a simple function that converts any angle to radians. It 
    is usually RADIANS(x). To use this, go to cell B1 and type =RADIANS(A1). The 
    "equals" sign in this formula is standard spreadsheet-eze that tells the 
    spreadsheet you want a formula and not literal text (try typing it without 
    the equals sign and see what you get). You'll note that the Google Docs 
    spreadsheet is very clever about this and provides you with the acceptable 
    function options as you type. First it shows you all functions that start 
    with "R", then all that start with "RA"... By the time you have type =RAD, 
    the only option left is =RADIANS. When you're done typing the formula, press 
    ENTER or move to another cell, by clicking or using the keyboard arrow keys. 
    The cell will display 0.785398... almost immediately. Note that if you're 
    doing this in OpenOffice Calc instead, that product defaults to displaying 
    two digits after the decimal place so it shows 0.79. You can change this, but 
    all you need to know right now is that the number isn't rounded. It has the 
    full accuracy value hidden from view. And so that we can get to a 
    trigonometric result right away, let's enter one more formula. In cell C1, 
    enter =SIN(B1). And there's that familiar 0.707... (which is in fact the sine 
    of 45 degrees, equal to the square root of 0.5).
    So far we've managed to do something that you could do using a calculator with 
    a few keystrokes. It's not much benefit. But the key feature of a spreadsheet 
    that makes it so useful is that the formulas are always present "behind" the 
    numerical values that we see. Go back to cell A1 and type 50 instead of 45. 
    The other cells in B1 and C1 update to show the correct values for an angle 
    of 50 degrees. 
    Next, cell references are relative (usually). That is, our formula in cell B1 
    which reads =RADIANS(A1) is actually saying 
    =RADIANS("the cell one to the left of this cell"). If we copy and paste these 
    cells to a new location, their formulas will immediately reflect the changed 
    By the way, where ARE the formulas? All spreadsheets have a field or a "box" 
    where you can see the formula underlying the currently highlighted cell. In 
    the Google Docs spreadsheet, that field is down in the lower right. But 
    there's a way to make it more prominent and more consistent with other 
    spreadsheets. Go to the "View" menu (up top) and select "Formula Bar". Now 
    you see a line that says "Formula:" right below the main toolbar above the 
    spreadsheet table. If you click on cells B1 and/or C1, you can see the 
    formulas that you typed previously.
    Now let's see that "relative" referencing aspect of these formulas at work. 
    Click on cell B1. Drag through to C1 so that those two cells are highlighted. 
    Go to "Edit", select "Copy". Then move the cursor down a row to cell B2 and 
    do "Edit" and "Paste". Pretty cool, huh? We made two ZEROS. Ok, maybe not so 
    cool. Why are they zero? Click on those cells, B2 and C2, and you will see 
    that their formulas are slightly different. They read =RADIANS(A2) and 
    =SIN(B2). They have the same referencing structure as the row above. Each 
    cell "points" to the cell immediately to its left. So if we want to see 
    numbers in there that aren't zero, we need a non-zero angle in cell A2. Try 
    typing any angle there, for example, 60 (that's 60 degrees, by the way). And 
    sure enough, the formulas update and we see the sine of 60 degrees in column 
    One last trick for this lesson. Go back to cell A1 and type a zero there (any 
    good table of sines will start at zero). Now go to cell A2 and type =A1+1. 
    And there you have the first two rows of a sine table (for integral degrees; 
    change that +1 to another value if you want). But this is where things get 
    really efficient, and rather fun. Select cells A2 through C2 with your mouse. 
    Copy them. Then click on cell A3 (the next available row) and paste. And 
    there's row three with hardly any work at all. Now click cell A4 (the 
    left-most cell in the next empty row) but this time, before you paste, drag 
    down five rows so that you have selected five cells in column (they should 
    all be highlighted --see attached image). Paste again. Now you have a bunch 
    of rows in a sine table!
    That should be a good start. You should be able to make small tables of 
    cosines, or logtangents, or haversines or any similar functions easily. Don't 
    forget to save your work: click that little note in the lower right that says 
    "Start Autosaving" or go to the "File" menu.
    Some tips and tricks tomorrow...
    PS: The attached image shows this spreadsheet (almost) as it looked on my 
    machine just BEFORE the final "Paste". I was working in IE in Windows but it 
    should look nearly the same in Safari on a Mac.
    Navigation List archive: www.fer3.com/arc
    To post, email NavList@fer3.com
    To unsubscribe, email NavList-unsubscribe@fer3.com


    Browse Files

    Drop Files


    What is NavList?

    Join NavList

    (please, no nicknames or handles)
    Do you want to receive all group messages by email?
    Yes No

    You can also join by posting. Your first on-topic post automatically makes you a member.

    Posting Code

    Enter the email address associated with your NavList messages. Your posting code will be emailed to you immediately.

    Email Settings

    Posting Code:

    Custom Index

    Start date: (yyyymm dd)
    End date: (yyyymm dd)

    Visit this site
    Visit this site
    Visit this site
    Visit this site
    Visit this site
    Visit this site