I need help with a file that has many nulls. All nulls need to be replaced with a numerical value from the closest date. I've been able to solve for a single null that has a value above it with a multi-row formula. However, I need help with a solution for the "1/11/2015" date. The "FX_RATE" should reflect 0.8431 (I'm rounding for the example). Thoughts? Suggestions?
Case 1 and Case 2 would be my desired solutions. Is there a way to modify their formulas so that it resolves all nulls? As of now I am left with nulls for "8/17/2015" and "8/23/2015". Those dates have multiple nulls consecutively. Thoughts?
5) Predicted value using linear regression or a time series algorithm based on the known valid values
Once your general rule is determined you may want to consider enhancing the general rule and use branching (FILTER tool) when the duration between known values exceeds a defined threshold:
e.g. If the sequence is daily and the values gap to fill is one missing value (one day) then use algorithm X but if the gap is 15 days and there are 15 NULL values in sequence then use a different algorithm Y etc.
As part of your solution strategy you may want to first evaluate your data to discover and tag every consecutive sequence of NULL values. For this data you could use the Interpolation or predictive options referred to above.