Access or Excel question: Convert billing cycles into natural months?

Factual Questions
1

From a utility, let’s say I get data like this:



kWh Used | Meter Read Date
=========|================
56       | 12/16/2010
78       | 11/14/2010
69       | 10/12/2010
33       | 9/16/2010

Where the meter is read on a random day APPROXIMATELY a month after the previous date. I want to analyze that by month instead of billing cycle (i.e., usage for October and November).

I’m thinking of doing it by averaging and adding:

For October, it would be (69 * 12/31) + ( 78 * (14/30 + 31-12))

Meaning I’d use the first 12 days from the October billing cycle and then the remaining 18 days of October using data from the November billing cycle.

Does that seem logical, and is there an easier (maybe built-in?) way to do this?

Thank you!

2

It is somewhat logical.

I know of no built in way to do it. Basically I would do it the way you said - in excel - with a lookup table for each month indicating length of month (yes you could do it with a fancier formula, but I’m lazy).

FWIW - if you are analyzing your own personal data - very rarely have I found adjustments like these to improve the analysis. It is easier to just pretend the readings for September are for all of September.

I’ve made adjustments like this to accommodate data from different timezones and such. Even when presenting the data to smart people - and explaining what I did - no one but me seems to care. It never occurs to other people to think about these things (or very rarely).

3

I just noticed a few errors in that formula (missed September’s contribution to the October billing cycle, reversed November & October in the second part).

I don’t think my boss would be very happy with that :slight_smile: It’s for a university that uses several MWh a month across a few dozen buildings and the bad approximations add up quickly.

4

Well then by all means - get as particular as you can :slight_smile:

I assume you know about the MONTH function? It will give you the month (1-12) from most standard date formats. You can use that as a start - or there maybe another way, but that is how I’d do it. MONTH to get a number from 1 to 12 - and then use VLOOKUP to get max days from a manual 12x2 table. And then use the formula you hinted at. Sort of assumes you have data from every month.

5

That actually kind of what utilities have to do when we do 10-Ks and 10-Qs (but backwards). We pro-rata out the amount of unbilled days to estimate how much service we’ve delivered without billing, since it’s technically an asset.

That said, I think you have a slightly easier/better way to do the formulas.

You can actually subtract one date from another in Excel and you will get a number of days as an answer.

So, what I would do for October would be:

([69 / (10/12/2010 - 9/16/2010)] * day(10/12/2010) + (78 / (11/14/2010 - 10/12/2010) * ([eom(10/12/2010,0)] - 10/12/2010)

Of course, the dates would be cell references, so you should be able to put the whole thing together without any hard codes or vlookups.

eta: the eom function take any date as an input and produce the last day of that month plus however many months you put after the comma. So, eom(6/5/2013,1) will equal 7/31/2013. A 0 will give you the last date of the month entered.

The day command will take any date as an input and output a number equal to the day of the month. So day(5/7/2013) equals 7.

6

This is better than my idea. Didn’t know about eom function - might come in handy for me - if only I remember it’s there…

7

I did it using a crude combination of a VLOOKUP and a pivot table. Here is the spreadsheet: http://slamfrog.com/kw.xls

Here is the answer for the sample data. It is also in the pivot table highlighted in yellow in the spreadsheet.



Month  Total
9	72.808
10	76.465
11	60.477
12	26.250

Hopefully, it is correct. :slight_smile:

8

Thank you all for the help. It took me a few weeks to make and debug, but ultimately I did average out usage across neighboring billing cycles. The eomonth() function helped tremendously.

Here’s the finished product if you anyone wants to take a look:
http://asdfv.com/bills.xlsx

Thank you all again!