I need a template to record my daily electricity usage

[Fhazel Johennesse]

I live in South Africa and you may know that our supply of electricity from
our monopoly supplier cannot meet demand so we are forcibly rationed with
rolling blackouts (which means we all have turns to have whole suburb's
supply cut. I want to save electricity but I need to measure it first and
record it and then compare to previous usage to see if I am indeed saving. I
need a template that will allow me to enter my daily usage (I will take a
reading at the same time everyday) and then build up a history so that I can
see which days heaviest usage occurs (and why!) over months and hope to see a
downwards trend. Also, it would be great if the template could predict costs
by allowing a price to be entered for usage and so one could see costs per
day, per week and per month. It would be nice if the template had graphs for
days, weeks and months with usage and cost.

 

[Gord Dibben]

Column A is daily date.

A1 has a heading of Date
A2 has start date
A3 has formula =A2+1.........copy that down a buch of rows. Let's start with
90 days. You can extend that as you go.

Column B is daily reading of kwh

B1 has heading of Reading
B2 is starting daily reading.....manually entered from meter reading
B3 would be tomorrow when you enter the new meter reading.

Column C is daily usage

C1 has heading Usage
C2 has this formula =IF(OR(B2="",B3=""),"",B3-B2)
Double-click on B2 fill handle to copy down as far as you have dates in column A

Column D is daily cost

D1 has a heading of Cost/day
D2 has this formula =IF(C2="","",C2*$H$1) where H1 contains the cost/kwh

Work with this a while and you should be able to figure out the formulas for
weekly cost(7 days summed) and a month.

Prediction and charts are a little more complex.

The "Why" usage is high on a particular day would have to manually entered in
another column.


Gord Dibben MS Excel MVP

On Mon, 18 Feb 2008 09:46:01 -0800, Fhazel Johennesse <Fhazel

 

[Fhazel Johennesse]

Thank you very much. I am going to copy and paste your advice and build the
sheet this weekend. Once again, thanks.

 

[Gord Dibben]

For the weekly and monthly totals and averages a PivotTable would be a good
function.

See Debra Dalgleish's site for info on PT's.

http://www.contextures.on.ca/tiptech.html

Scroll down to PivotTable section.


Gord