Excel code or macro to interpolate between data points

[stadamack]

Electricity meter readings are recorded every 7 days and recorded in an excel spreadsheet. Date is recorded in Col 1. Corresponding meter reading is recorded in Col 2. From the data entered I would like to automatically calculate the consumption for each month. The code / macro should interpolate to determine meter readings at the beginning and end of each month - these can then be subtracted to determine consumption during any month.

 

[stadamack]

Electricity meter readings are recorded every 7 days and recorded in an excel spreadsheet. Date is recorded in Col 1. Corresponding meter reading is recorded in Col 2. From the data entered I would like to automatically calculate the consumption for each month. The code / macro should interpolate to determine meter readings at the beginning and end of each month - these can then be subtracted to determine consumption during any month.

 

[IRstuff]

Wny not use Excel's regression tools?

TTFN

FAQ731-376

 

[stadamack]

maybe there is a function in the regression tools to do this? have examined the trend function but this fits a line using least squares rather than linear interpolation.

 

[IRstuff]

linear interpolation only requires two points, so what's the problem?

TTFN

FAQ731-376

 

[stadamack]

linear interpolation is no problem. am trying to set up a spreadsheet to automate the process. data is input in 2 columns. Col 1 date. Col 2 meter readings. Is there a function that can take the following inputs:
- a value in the range in Col 1
- an array containing Col 1 + Col 2
and return a corresponding value from col 2.
A function that conducts linear interpolation as required.

 

[MacGyverS2000]

This question is more appropriate to Tek-tips.com and their Excel forums.

Dan - Owner

http://www.Hi-TecDesigns.com