# NavList:

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

Message:αβγ
Message:abc
 Add Images & Files Posting Code: Name: Email:
Re: formula for refraction
From: Frank Reed CT
Date: 2007 Mar 21, 20:42 -0700

```Bill, you asked:
"Here is where I have gone wrong.  Excel is returning the same value
in C2 as was entered in B2--40.5.
Any hints or tips?"

Yes, it turns out that the VLOOKUP function in Excel has a slightly
different definition from the one in the spreadsheet I was using. Just
change the third argument in each VLOOKUP from "1" to "2". Then it
works.

It turns out, though, that you can't use linear interpolation below
about five degrees if the spacing is single degrees. Instead you have
to use a spacing of about 0.25 degrees. Thus more typing! And the
corresponding formula is longer:
=VLOOKUP(INT(4*B2)/4,G11:H101,2)
*(1+INT(4*B2)-4*B2) +
VLOOKUP(INT(4*B2)/4+1/4,G10:H100,2)
*(4*B2-INT(4*B2))

If you have some other dataset where the spacing between the "x"
values is, let's say, 0.125 units, then replace every "4" in the above
formula by "8". As a general note, after you've entered a table of
values like the refraction table in this case, you should graph both
the numbers and the first differences to make sure you haven't made
any entry error. A typo will show up as a sudden jump in the graph.

-FER

--~--~---------~--~----~------------~-------~--~----~
To post to this group, send email to NavList@fer3.com
To unsubscribe, send email to NavList-unsubscribe@fer3.com
-~----------~----~----~----~------~----~------~--~---

```
Browse Files

Drop Files

### Join NavList

 Name: (please, no nicknames or handles) Email:
 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:

### Email Settings

 Posting Code:

### Custom Index

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