# NavList:

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

**Simulating sight data with spreadsheets**

**From:**Frank Reed

**Date:**2013 Jan 8, 11:09 -0800

It's cold out (for most of us northern hemisphere NavList members). Taking lots of sights for statistical experiments is not an ideal option in early January. But we can simulate them. If you have a recent edition of Excel or equivalent, you can generate columns full of normally distributed random numbers and then run trials on them. For example, you could ask: how many sample points should you have if you want some confidence in the observed standard deviation to one significant digit? And two significant digits?

Suppose you want some normally distributed numbers with SD=0.53 and mean=0.12. In any cell of your spreadsheet, enter this formula:

=0.53*NORMSINV(RAND())+0.12

Copy that formula and paste it into some number of cells below the first. Fill down maybe 100 cells. You can now try calculating means and standard deviations from those numbers. In most spreadsheets, since the earliest software from the 1980s, if you press the "F9" key on your keyboard, the spreadsheet will re-calculate, which in cases like this means that it will generate a fresh set of random numbers. So for example, if cells A1 to A100 contain you random numbers, a formula like =AVERAGE(A1:A100) in cell B1 will generate the average for the current sample set every time you press "F9" or equivalent.

If you want some "excess kurtosis", or in simpler words "more outliers", in your sample numbers, try this formula instead:

=IF(RAND()<0.1, 2.05*NORMSINV(RAND())+0.12, 0.53*NORMSINV(RAND())+0.12).

This is a special case of the standard spreadsheet function

=IF(condition, then_show_this, else_show_this).

Since the standard random number function, RAND(), generates random numbers uniformly in the range from 0 to 1.0, it follows that 90% of the time, this formula will generate normally distributed numbers with a standard deviation of 0.53, like the previous case. For the remaining 10%, this formula will generate normally-distributed numbers with a standard deviation of 2.05 (pick any number here). Adding in some outliers like this is, I believe, more realistic for practical celestial navigation sights of any type. Note that the simulated s.d. is no longer 0.53. It is now somewhere between 0.53 and 2.05. A simulation like this with built-in outliers intentionally breaks the rules about the number of points found beyond the standard 2sd and 3sd limits.

Now you've got something to do if it's too cold outside. Those in the southern hemisphere have no excuse. :)

-FER

----------------------------------------------------------------

NavList message boards and member settings: www.fer3.com/NavList

Members may optionally receive posts by email.

To cancel email delivery, send a message to NoMail[at]fer3.com

----------------------------------------------------------------