Rearranging irregular data to monthly format

[dpydotsmw]

Hi,

I have a spreadsheet of meter readings for household utility usage
(gas, electricity and water). The readings have been taken at
irregular dates, which makes analysing the data hard. For example, it
would be great to be able to work out how much electricty got used in
the month of February 2009, and perhaps to compare that to how much
got used in February 2008 and 2007. However, I will not have taken
readings at 1st Feb and then on 1st March on any of those years. The
readings are taken pretty much when I remember to check.

So I have a worksheet with
- the date I took the readings in column A
- the gas meter reading in column B
- the electricty meter reading in column C

What I would like to do is to have a second worksheet where these
figures get tidied up. Column A would have dates starting on 1st
January and incrementing by a month in each row (1st Feb, 1st March,
etc). Then in column B I would need to use the data points which I do
have to estimate (based on daily usage over that period) what the gas
meter reading would have been on 1st Jan, 1st Feb etc. (Ditto in
column C for electricity.)

Is this actually doable using Excel functions, or do I need visual
basic?

If it's any help here is the spreadsheet:
http://www.filefactory.com/file/agabg3a/n/Utility_usage_xls

Thank you

 

[Luke M]

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.

 

[dpydotsmw]

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.