calculate monthly usage from varying dates

[jbergenudd]

I'm trying to construct a sheet where I can log my electricity use. Around the first of the month i record what my meter specifies.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]2016-01-02[/TD]
[TD]67523[/TD]
[/TR]
[TR]
[TD]2016-02-01
[/TD]
[TD]69230[/TD]
[/TR]
[TR]
[TD]2016-02-28[/TD]
[TD]71456[/TD]
[/TR]
[TR]
[TD]2016-04-10[/TD]
[TD]73810[/TD]
[/TR]
[TR]
[TD]2016-05-02[/TD]
[TD]74996[/TD]
[/TR]
[TR]
[TD]2016-06-01[/TD]
[TD]77120[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to approx. calculate monthly usage? In april I was away and couldn't check until 10 days into the month so that reading should be recalculated to what it "should" have been on 2016-04-01. From that I can calculate average monthly usage.

Anybody that have tackled this problem?

Regards,
/Jonas

 

[jorismoerings]

Hi,

I understand what your trying to do but don't understand why you want to restate the number.
As you calculate a monthly average you'll take end reading of last month -/- start reading of first month and divide that number by the number of months.
So that number won't change by restating the march usage.

But answering your question: This can only be done by using the assumption all days of the period have a similar effect on usage (eg every day of month as equal use)
That's not a most likely scenario but for calculation sake.

take a look at this:

Excel 2016 (Windows) 64 bit
	A	B	C
1	date	Meter Reading
2	2-1-2016	67,523
3	1-2-2016	69,230
4	28-2-2016	71,456
5	10-4-2016	73,810
6	2-5-2016	74,996
7	1-6-2016	77,120
8
9
10	Av. Usage per day	64,0
11
12		Av. Monthly Usage	Restated Meter Reading
13	Start		67,395
14	jan-16	1,984	69,379
15	feb-16	1,856	71,235
16	mrt-16	1,984	73,219
17	apr-16	1,920	75,139
18	mei-16	1,984	77,123
Joet

Cell Formulas
Range	Formula
B10	=ROUNDUP(((INDEX(B2:B7,MATCH(LARGE(A2:A7,1),A2:A7,0))-INDEX(B2:B7,MATCH(SMALL(A2:A7,1),A2:A7,0)))/(LARGE(A2:A7,1)-SMALL(A2:A7,1))),0)
B14	=((EOMONTH(A14,0)-A14+1)*$B$10)
C13	=B2-((A2-A14+1)*B10)
C14	=C13+B14

 

[jbergenudd]

Thanks! I'll try those formulas.

I realize that I can't rely on the usage being the same every day. but that assumption is better than trying to calculate average monthly usage when the "months" are anything from 20-40 days depending on when I can check the meter.