If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rearranging irregular data to monthly format
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/agab...lity_usage_xls Thank you -- Remember Dr Folkman |
#2
|
|||
|
|||
Rearranging irregular data to monthly format
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* " wrote: 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/agab...lity_usage_xls Thank you -- Remember Dr Folkman |
#3
|
|||
|
|||
Rearranging irregular data to monthly format
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 |
Thread Tools | |
Display Modes | |
|
|