Tom asks:
I have three values with wind direction at differrent altitudes.
A1: 020, A2: 2000 feet,
B1: 010, B2: 5000 feet,
C1: 350, C2: 10000 feetand I would like to forecast the wind direction for D2: 3000 feet. I’ve tried the forecast function but it goes haywire with the change from 010 to 350 degrees.
I made an assumption and I don’t know if it’s valid. I assume that all three wind directions will be within 180 degrees of each other. That is, if the wind is 090 at 2,000 feet, it won’t be 270 at 5,000 feet. If it is, you probably don’t want to be flying in it anyway. If that assumption holds, here’s what I came up with:
Note that that’s an array formula entered with Control+Shift+Enter, not just Enter.
If I have a range of wind directions that crosses zero (left), I shift it 180 degrees (right), do that deed, and shift it back. First I determine if the difference between the MIN and MAX is more than 180. Since I’m assuming that no three wind directions will be more than 180, if they are they must cross zero. For those that don’t cross zero, it’s a pretty straight forward FORECAST formula,
.
If they do cross zero, there’s a little more work to be done. To rotate them around 180 degrees, I use the MOD function on the x-axis argument,
.
Then I rotate that result around 180 degrees again using the same method.
I converted the wind directions to range from [-180,180] by using atan2(cos(theta),sin(theta)). Then a linear interpolation like Forecast works just fine.
Dick – Pretty clever. My approach was adding 360° to “selected” directions, getting the FORECAST, and MODding by 360. Basically the same, except I eyeballed the “selected” directions, rather than coming up with an algorithm like your test that they are within 180°. By the time I got there, I decided your approach was as valid as mine.
T – I tried your way, and got irregular results. Converting to [-180,180] changes the directions in Dick’s fourth case from {190,170,200} to {-170,170,-160}. Instead of varying by 30°, they now vary by 340°. The R-squared value of the resulting FORECAST is 0.014, compared to 0.980 for the lowest R-squared of the other cases.
The trouble with the Forecast() function is it fits a straight line to your data, which isn’t necessarily what you want to do. For instance in your fourth example, if you enter something close to 5000 you will get a result of about 185, rather than 170, which I’m guessing is what you’d want. What you often need is a piecewise straight line interpolation; i.e. for values between 2000 and 5000 fit a straight line between those two values, and for between 5000 and 10000 use a different straight line. There should be an Excel function to do that, but there isn’t. Plenty of UDFs that will do the job though (including at http://newtonexcelbach.wordpress.com/2008/08/10/intersections-interpolations-and-rotations/)
I wrote up a more graphical approach today, which still uses Dick’s clever formula, but uses real charts instead of those silly pies to help describe his 180° double secret rotation trick. It also showed the error of T’s ways. The post is called Calculating Wind Direction per DDoE.
I thought this looked familiar: http://www.dailydoseofexcel.com/archives/2005/08/15/wind-direction-chart/
Amazing to think.. this blog has been running for 4 years.
[…] Dick frequently chooses topics that are arcane, but curiously appealing topics such as “Calculating Wind Direction” and “Charting […]