Weather data

[John Hellis]

Hi

I used wolverineb’s formula
http://www.mrexcel.com/forum/showthread.php?t=458054&highlight=wind+direction) in reverse to calculate the wind direction in degrees from wind direction in text.
The lookup table is

This works well after a little tweaking.

The formula I am using is =VLOOKUP(N5,V2:W17,2,0)

Now I need to copy this about 4000 times (the monthly data collected from my weather station) to then be able to get the average wind direction for the month.

When I use the usual copy methods – bottom right hand corner of the cell and drag or copy and paste I get a change in the formula. for example =VLOOKUP(N6,V3:W18,2,0)

Is there any way to copy a formula with only changing the lookup value without altering the table values.

I am using Excel 2003.

 

[VoG]

Try making the row references absolute

=VLOOKUP(N6,V$3:W$18,2,0)

 

[Ruddles]

Correction (if I may be so bold):-

=VLOOKUP(N6,V$2:W$17,2,0)

 

[VoG]

Oops! Copied the wrong formula

 

[John Hellis]

OMG

I have spent hours trying to find an answer to this, I am so greatful.

Thanks you so much.

John

 

[VoG]

You are welcome. By way of explanation see http://www.cpearson.com/excel/relative.aspx