# NavList:

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

**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 -~----------~----~----~----~------~----~------~--~---