NavList:
A Community Devoted to the Preservation and Practice of Celestial Navigation and Other Methods of Traditional Wayfinding
Re: Navigation spreadsheet lessons
From: Frank Reed
Date: 2009 Jan 13, 20:28 -0800
From: Frank Reed
Date: 2009 Jan 13, 20:28 -0800
A few spreadsheet tricks. Let's do some very basic statistics on a block of numbers. I'm going to continue working in the same spreadsheet, but you could open a fresh one. Let's go to cell H2. At this point, you might be wondering how far to the right you can go in columns and how far down in rows. For most spreadsheet software these days, there's a more-or-less endless supply of rows and columns, but because the Google Docs spreadsheet app works online, there are fewer displayed by default. You can add more easily using the "Insert" menu. And specific to the Google spreadsheet, there is a little "+" sign at the lower left of the spreadsheet that lets you add rows in bulk. Also note that you can insert columns or rows *between* previously entered columns or rows of data and formulas without losing the relationships. The spreadsheet automatically adjusts. In cell H2, enter some number off the top of your head. I'll enter 34.5. Now use the arrow keys to go down one cell at a time and enter in some more numbers off the top of your head in cells H3 through H7 (these aren't really "random" numbers, which the spreadsheet can actually create for you, but that's the idea --some numbers picked at random). Now go the next column to the right and do the same thing in cells I2 through I7. You now have twelve numbers in a little table. What is the average of those numbers? Spreadsheets were built for this sort of thing. Go to the top of the table and in cell H1, enter the formula =AVERAGE( ...but don't type anything after the open parenthesis. Instead hit the down arrow key on your keyboard. The spreadsheet automatically fills in H2. Next hold down the shift key and, again using the keyboard arrow keys, mark out (select) all of the cells containing the numbers you entered. Then hit the 'enter' key. Now look at the formula that the spreadsheet created here (remember: if you want the formula bar, select it from the "View" menu). The formula says =AVERAGE(H2:I7). This notation is more of that "spreadsheet-eze". Two cell designations with a colon between them means that we want to do something with *all* of the cells in the block that has those corners. And sure enough, the average that you see calculated there is correct for that whole block of numbers. You can try doing other statistics on that block of numbers. For example, go to cell I2 and enter the formula =STDEV( and then mark out the same block of cells, or type the range in yourself, to complete the formula as =STDEV(H2:I17). This gives you the sample standard deviation of those numbers. I have a small project I need to complete tonight, so that's all for now. Tomorrow, we'll create a dip table, do some absolute cell references, maybe some formatting, and a more complicated navigational equation. -FER PS: In your last message you wrote: "Thank you, thank you for this tour." Sure thing. I'm enjoying it. And: "The google.com site said my Safari wasn't included." That's odd. It works on my Mac. I bet your copy of Safari is older. Have you let your Mac do its auto-update thing recently? --~--~---------~--~----~------------~-------~--~----~ Navigation List archive: www.fer3.com/arc To post, email NavList@fer3.com To , email NavList-@fer3.com -~----------~----~----~----~------~----~------~--~---