On 8 Apr, 20:41, Luke M wrote:
A lot depends on how you want to calculate? You could use a linear trend of
entire data, or some type of rolling window. For simplicity, here's how you
could do a linear calculation to get estimates.

Go ahead and setup your column A starting on row 2 on Sheet2 going 2/1, 3/1,
4/1, etc., either through formulas or just let XL figure out the pattern.

Then, in B2:
=TREND(Sheet1!B$2:B$100,Sheet1!$A$2:$A$100,$A2)
Copy this over to C2, then copy down to match your dates you setup.

Note that the ranges in the TREND function must bet set to the data you
actually have. DO NOT INCLUDE BLANK CELLS!

Again, there are various ways to approach this, but it depends on what kind
of statistical analysis you want to do. As a heads up, you might take a look
at the LINEST or GROWTH functions, depending on how you go.


Thanks Luke. I've just tried the TREND function with interesting
results. I don't pretent to understand much about the algorithm on
which it's based, but I think it's basically finding the curve of best
fit through a range of data points? Just visually checking the figures
it comes up for the notional readings on the 1st of each month against
the real data suggests that it's often a bit out, and I think it's
trying to be more clever than I need. I guess in graphical terms that
to work out each 1st-of-the-month notional reading I just need to find
the two real data points either side of that date, draw a straight
line between them and read off on the Y axis what the meter reading
probably would have been then.

--
Remember Dr Folkman