Need to interpolate large pump flow record data set
Need to interpolate large pump flow record data set
(OP)
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.
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.





RE: Need to interpolate large pump flow record data set
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
RE: Need to interpolate large pump flow record data set
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Need to interpolate large pump flow record data set
http://newtonexcelbach.wordpress.com/2012/10/08/da...
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.com/2012/09/30/da...
for download.
Please ask if you have any problems using the functions.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Need to interpolate large pump flow record data set
CODE -->
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