Filling in Gaps in Broken Data

[Richardstokes88]

Hi Chandoo!

I've been puzzling for a while now over how to fill in gaps in my data. I work with energy meters and often they are missing occasional readings. I would like to simply plot the missing values with linear values in the gap. This works using the median formula for one gap but it goes awry with 2 consequential gaps and doesn't work at all for 3 or more.

Seems straight forward at first but must be a common problem.

I've attached a workbook which hopefully helps explain my problem. If anyone has an idea for how to best solve this I'd really appreciate it.

Thanks team!

Richard

 

[NARAYANK991]

Hi Richard ,

What is to be done if the first or last entry is not available ?

Narayan

 

[JD Hen]

One option is to use iterative calculations as mentioned on http://chandoo.org/wp/2010/09/16/excel-circular-references/

If you enable iterative calculations and change the formula in column D such that D3 would be changed to =IF(C3=0,MEDIAN(D2,D4),C3) then you will get gaps at an equal distance between the last and next actual value (it's actual just calculating the mean between the two).

The main issue with this is that iterative calculations will be enabled everywhere.

Another solution would be to essentially replicate what is happening with a formula: find the last and next non-empty cells and use these to create data points at equidistant gaps between them.

I managed to come up with a formula entered in E3 as
=IF(C3=0,(LOOKUP(1,--($C$1:C2<>0),$C$1:C2)+((ROW()-LOOKUP(1,--($C$1:C2<>0),ROW($C$1:C2)))*E4))/(ROW()-LOOKUP(1,--($C$1:C2<>0),ROW($C$1:C2))+1),C3)

It's obviously quite long compared to iterative calculations, although it might be possible to shorten it. The basic principle is for missing data:
(Last actual data) + (gap between this row and the last actual data row) * (data in cell below) / (gap between this row and the last actual data row + 1)

Sorry if that's unclear, it's quite difficult to explain.

 

[Richardstokes88]

JD Hen,

That works a treat. The written logic makes sense I just haven't used those formulas before so was struggling to achieve this outcome.

Many thanks

Richard

 

[Richardstokes88]

Narayan991,

In the event that the first or last data point is missing I would request it from my data source, or just run the analysis on the available data range. I was mostly concerned with the gaps.

Thanks

Richard