×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Need to interpolate large pump flow record data set

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.


RE: Need to interpolate large pump flow record data set

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

RE: Need to interpolate large pump flow record data set

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: 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

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/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

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

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources