Hi,
I am trying to create a report on my energy usage and bills so I can monitor trends. My energy meter and billing cycle is not consistant and I realise that as such I am not going to get extremely accurate reports but I would like to try and put the data into a form of month based on averages.
For example assume I had the following data. My table is far for detailed with breakdown of peak / offpeak usage and costs,
So what I want to do is basically find a way which would look at formatting the above to the following automaticallyPlease Login or Register to view this content.
March 07 - Inconclusive date due to not having data prior to 21/3.
April 07 - Use the average from Period 1. 372kW / 50 Days = 7.44
May 07 - Use the average for Period 1 from 1/5/07 to 10/5/07 (7.44*10), Average from Period 2 for 11/5/07 to end of May (20 Days) at an average of 443kW / 89 Days = 4.98. Therefore (7.44*10 + 4.98*21) / 31 Days of May = 178.98/31 Days = 5.77
June 07 - Use the average from Period 2. 443kW / 89 Days = 4.98
July 07 - Use the average from Period 2. 443kW / 89 Days = 4.98
Aug 07 - Use the average for Period 2 from 1/8/07 to 8/8/07 (4.98*8), Average from Period 3 for 9/8/07 to end of August (22 Days) at an average of 412kW / 110 Days = 3.75. Therefore (4.98*8 + 3.75*22) / 31 Days of May = 122.34/31 Days = 3.95
and so on. Not sure if there is a function which may be able to do this automatically or not but it would sure make monitoring trends a lot easier if there was.
Any advise you can give on how I may achieve this except for the painstaking way of going through and manually creating a line for each month on the surrounding data around it.
Bookmarks