calculate monthly usage from varying dates

jbergenudd

New Member
Joined
Apr 24, 2014
Messages
6
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
ABC
1dateMeter Reading
22-1-201667,523
31-2-201669,230
428-2-201671,456
510-4-201673,810
62-5-201674,996
71-6-201677,120
8
9
10Av. Usage per day64,0
11
12Av. Monthly UsageRestated Meter Reading
13Start67,395
14jan-161,98469,379
15feb-161,85671,235
16mrt-161,98473,219
17apr-161,92075,139
18mei-161,98477,123
Joet
Cell Formulas
RangeFormula
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top