Linear Interpolation of Values

[jbh1128d1]

I have a data set that I have some values that need to be interpolated values and extrapolating others.  The data is set up as the same:

RecordID    Model    Variable     Level    Value

The formula to do this in excel is as follows if one value is null:

Row-1:Value]+([Row-1:Value]-[Row+1:Value])/([Row-1:RecordID]-[Row+1:RecordID])

That works and is verified.

However, if there are two null values, or three null values, then you choose the value before and the next value with that value set.  The first question surrounds how to choose the next filled in value and set that?  

Example: 

The above shows two null values.  If I were to use excel, it would be 656Value+(656Value-659$Value$)/(Row656-Row$659$).  Obviously, the $ denotes the values would be set.  There are six different variables, and they are their own group.  How can I do this with Alteryx?

Data is attached.  

[BenMoss]

Hi,

This was a fun challenge.

Here I have built a solution for you to identify the closest non-null value before and after the null record.

This should then allow you to apply your formula against the 'Source_Value' column.

Either way this should give you a good start.

Ben

[Philip · Accepted solution]

Here's my solution. The only thing that it calculates incorrectly are nulls with no known values after, but ran out of time to solve it. 

[PaulDalen]

Ben, I think this will solve a problem I'm having as well. Can you please share the workflow so I can see the formula you used to solve the problem? the file you posted is only the post-processed data.

Thanks!

[star-stuff]

Thanks Philip, this is exactly what I needed and could not find anywhere else. My data set was also missing values at the end, but decided just to remove this row, always a choice :).