Replace nulls within ordered data

[TSP]

Hi everyone, I've searched the forums but I can't find a solution to this problem.

I have an ordered, evenly spaced list of Intervals. Some of the values are known and some are null.

For those that are null, I'd like to replace the null with the straight-line point between the two closest known values.

So if I have 1 _ _ 4 then i'd like to get 1 2 3 4 as my result etc.

I've explored using multi-row formulas to find the difference from the closest lower & greater known values but I can't get to where I need.

Any help greatly appreciated.

p.s there's no relationship between Interval & Value (so you can't just set Value equal to Interval).

[JosephSerpis]

Hi @TSP you can use the Multi-Row tool to solve that challenge

[TSP]

Thanks @JosephSerpis are you able to share how?

[JosephSerpis]

Hi @TSP I mocked an example that produces the results you described.

[TSP]

Thanks @JosephSerpis unfortunately this assumes there's always a value change of 1 between each interval which isn't the case.

The process needs to find the variance and number of intervals for each null to work out the incremental change.

e.g 5 _ _ 20 _ _ 23

Should return 5 10 15 20 21 22 23.

Many thanks,

[ArtApa · Accepted solution]

Hi @TSP - Here is a possible solution:

[TSP]

Thanks @ArtApa this is almost identical to the solution I eventually got to.

I re-sorted my data based on an initial RecordID but otherwise the same approach.

Many thanks,

TSP