Interpolating between degrees

Hi

I'm trying to come up with a solution to interpolate between wind directions associated with different altitudes.

The table:
ALT FL180 FL190 FL200 FL210 FL220 FL240
DIR 300 x x x x 030

The values 300 and 030 are examples of user input and the x's should be calculated.

Wind can veer (change clockwise) or back (change anticlockwise) and I have been trying, unsuccessfully, to account for this.

When the direction reaches 360 the next point should be 001, and likewise when decreasing from 001 to 360.

I appreciate any assistance, thanks!

Ok. I have attached a solution for you. The green cells are the user inputs, and the four cells between them do the interpolation regardless of direction and does this based on the shortest change. For example, if The user input is 40 and 300 it will not interpolate as 40, 92, 144, 196, 248, 300. It will instead interpolate as 40, 20, 0, 340, 300. I'm assuming this is what you want. The formulas are somewhat complex to understand so here is my basic explanation. If will start with the nested IF().

Formula:

Please Login or Register  to view this content.

This part of the formula is getting the difference between the user inputs and the direction of change from one to the other. It first checks if the difference between the 2 is less than 180 degrees. If it is, then it gets the difference between the two. This is the simplest case because you can directly interpolate between the two without having to cross over the 360 degree mark. If the difference between the 2 is more than 180 degrees, then it check to see which of the 2 numbers is smaller and then it adds 360 degrees to that number and then gets the difference between the two numbers. Now the hard part is done. The rest of the formula is simple.

Formula:

Please Login or Register  to view this content.

For the first formula you take the first input and change it by the above calculated difference and multiply by 1/5 since you are interpolating 4 steps between the two numbers. There are 4 steps between the two numbers (1/5, 2/5, 3/5, and 4/5). Since you want from 1 to 360 and not 0 to 359, I subtract 1 before using MOD (this function gives the remainder when a number is divided by another number... in this case 360) that way it doesn't convert 360 to 0, but it will convert -1 to 359. Then I add one after the MOD is calculated to get you back up to 1 through 360 instead of 0 through 359. Hopefully this helps you understand the formula.

It is easiest to just open the workbook I attached and see how it all works. Good luck.

Hi brant.wheeler

Thank you so much for the solution. My solution had some nested IF statements too but for the life of me I couldn't figure out how to reach 360 and carry on from 0.

Cheers.