Find and Replace Nearest Value

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?

The file is attached.

FX_DATE.xlsx

Tips and Tricks

Macros

Interface Tools

Accepted answers

MarqueeCrew

@c3reynol,

If you sort the data in DATE ascending order, you can use a multi-row formula as follows:

IIF(IsNull([FX_Rate]),[Row-1:FX_Rate],[FX_Rate])

It fills the Null values for you.

Cheers,

Mark

derekbelyea

I suggest that you start by defining the business rules you want to follow. For missing values ("NULLS") in a time sequence you can consider these choices:

1) Same value as nearest previous non-NULL value (eg. {5, NULL, 10} becomes {5, 5, 10})

2) Same value as next following non-NULL value (eg. {5, NULL, 10} becomes {5, 10, 10})

3) Average value using the nearest previous and next following non-NULL values - (eg. {5, NULL, 10} becomes {5, 7.5, 10} AND {5, NULL, NULL, 10} becomes {5, 7.5, 7.5, 10})

4) Interpolation (e.g. {5, NULL, NULL, NULL, 25} becomes {5, 10, 15, 20, 25})

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.

All comments

MarqueeCrew

@c3reynol,

If you sort the data in DATE ascending order, you can use a multi-row formula as follows:

IIF(IsNull([FX_Rate]),[Row-1:FX_Rate],[FX_Rate])

It fills the Null values for you.

Cheers,

Mark

c3reynol

That still leaves me with a null for the 1/11/2015 date. Can you help with that example?

c3reynol

This solution works when the "Update Existing Field" radio button is selected.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703