Need to interpolate large pump flow record data set

[jartgo]

Greetings,
I consider myself an expert user of basic spreadsheet functions. I'm familiar enough with macros to give them a try, and not at all familiar with VBA but also willing to try it if necessary to solve this problem. The problem is quite simple. I have a dataset of sporadic daily flow meter readings over several years. The data is in two columns. Column A is date and Column B is meter reading. The reading dates are random with some days skipped. For example Row 1 might be 8/11/08, Row 2 might be 8/17/08, Row 3 might be 8/18/08, and Row 4 might be 8/25/08, etc.

What I want to do is to have excel add rows in between the dates, such that I would then have a row for each calendar day (using the example above it would add rows for 8/12/08 through 8/16/08, and then 8/19/08 through 8/24/08). I would then like for it to interpolate the meter readings for the inserted rows/days, using the meter readings tied to the current data (again using the example above, it would interpolate between 8/11/08 and 8/17/08 to populate the meter readings for the the days 8/12/08 through 8/16/08, etc.)

This is done easy enough manually, but it obviously is a time consuming process. Any suggestions on how to automate this process?
Thanks in advance.

 

[pmover]

i'm not sure what the end goal or objective is with "creating data" that does not exist.

i'd approach the analysis a little differently and working with the data available . . .

the data start and end dates with meter data are available.
the number of days with and without data can be determined.
create a histogram. you will need to generate the bin data.
use statistical functions for max, min, median, etc.
since data exists for several years, perhaps comparisons can be made for days in years with data to those days in years without data.
perhaps comparing data on a yearly basis can be done additionally.

to automate will take time as well. hope this helps and good luck!
-pmover

 

[IRstuff]

The general approach would be to create a spline or regression fit, and use that to calculate the interpolated values. However, I do agree that since there is no new data, only the original data should be used for whatever you're trying to calculate

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[IDS]

With the help of some user defined functions it's quite easy in Excel to set up a list of dates then use cubic splines to interpolate from your data. See:

http://newtonexcelbach.wordpress.com/2012/10/08/daily-download-22-splines-and-curves/

for more details and a download file.

You can also use a least squares fit, rather than fitting the curve exactly through every point; see:

http://newtonexcelbach.wordpress.co...wnload-13-the-alglib-maths-library-and-excel/

for download.

Please ask if you have any problems using the functions.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[zelgar]

Here's a little macro that will fill in the dates and linearly interpolate the data between the 2 know fixed points:

Sub fill_in_data()
'
'
Dim i, irow, diff, slope_end As Integer
' Start irow = to 2nd value of data
irow = 3
Do Until IsEmpty(Cells(irow, 1))
    diff = Cells(irow, 1).Value - Cells(irow - 1, 1).Value
    slope_end = Cells(irow, 2).Value
    If diff > 1 Then
        For i = 1 To diff - 1
            Rows(irow).Insert
            Cells(irow, 1) = Cells(irow + 1, 1).Value - 1
            Cells(irow, 2) = (diff - i) / diff * (slope_end - Cells(irow - 1, 2)) + Cells(irow - 1, 2)
        Next i
    End If
    irow = irow + diff
Loop
'
End Sub