MS Excel Speedsheet Formula for Meter Reading Record

Forums › Off topic › MS Excel Speedsheet Formula for Meter Reading Record

Hacktrix

15 posts

Geek

#66426 18-Aug-2010 22:09

Hey there, I'm working on a speed sheet to help me keep record of my
electricity usage. I have 4 meters, I would like to telly up my daily usage
across all 4 meters.
I require a formula in MS Excel to help me achieve this.
Example:
Meter Reading Record
Serial Number        59802    59803    59804    59805
Type        General General    Range    Hot Water
Day    Date    Meter 1 Meter 2 Meter 3 Meter 4    Daily Usage
Thursday    8/19/2010    8290    3714 7148    9738
Friday    8/20/2010

So when adding Friday's data under Daily usage only the difference in the
total sum appears under daily usage for that day.

Hope it makes sense. Cheers

manhinli

2483 posts

Uber Geek

Trusted

#369580 18-Aug-2010 22:16

Replace C0, F0, C1, F1 with the appropriate cell names (where C0 = Thursday's details, C1 = Friday's details etc.)

Friday's "Daily Usage" -> =SUM(C1:F1)-SUM(C0:F0)

Then just autofill it and Excel should just automatically use the previous day's usage sum to compare to.

Find me on Twitter!

I posted 1, 2 x 10^3 times!

Hacktrix

15 posts

Geek

#369594 18-Aug-2010 22:38

Thanks, I did try that but it's not what I want.

Day
Date
Meter 1
Meter 2
Meter 3
Meter 4
Daily Usage

Thursday
8/19/2010
100
200
300
400

Friday
8/20/2010
101
202
303
404
10

Saturday
8/21/2010

More like this say, by updating the meter reading on a daily basic I can get the daily unit usage for that day

Hope this makes better sense, Also hope it is displayed correctly on post!

Hacktrix

15 posts

Geek

#369596 18-Aug-2010 22:41

its hasn't displayed correctly at all, speed sheet should be going across the page.

Any how say the usage on Friday has increased by 10 units over all meters so displays 10 in Daily usage

manhinli

2483 posts

Uber Geek

Trusted

#369598 18-Aug-2010 22:46

OK, here's a *spreadsheet* I've done for you on Google Docs, which you can download. :)

https://spreadsheets.google.com/ccc?key=0Aq5tTY_dDHsSdGhvbmpEWXRYb01JalNmOHpmaldnM2c&hl=en

(File > Download as > Excel to download)

Find me on Twitter!

I posted 1, 2 x 10^3 times!

Hacktrix

15 posts

Geek

#369609 18-Aug-2010 23:17

Thanks dude, maybe it's not possible want I want. But I only want the units usage

say Friday I enter on meter1 reading 1025
Then on Saturday enter 1030
Friday\Saturday usage would read 5 (it's gain if you like.)
but what would make it more complicated is adding 3 more meters and having the total gain not sum of all meter readings.

If your confused I understand..........so am I!

NonprayingMantis

6434 posts

Uber Geek

#369613 18-Aug-2010 23:36

Manhilni's looks exaclty like I woudlhave done it from your description, but maybe you are just not explaining it very well.
I *think* I know what you want. You want to enter a value into the cell for fridays reading, but have that same cell display NOT friday's reading but just the difference between friday and satruday, right? If so, I don't know why it has to be that way, but I wouldn't bother with that

I would add an extra set of 4 columns out to the right showing change for each meter individually.

so the first four columns show the actual readings (as you have now), the second four columns show the difference between that days actuals and the previous days, then simply add the 2nd four column together to get your difference

             Actual Reading (input values in this section)    Change (this section contains formulas)
             Meter 1 Meter 2 Meter 3 Meter 4                     Meter 1 Meter 2 Meter 3 Meter 4 Daily Usage
Friday     10        105        2050     289
Saturday 15        109        2072     301                        5            4          22       12       43

sop you enter in your readings in the left hand side, and the right hand side shows you the daily usage per meter, and the total.

Hacktrix

15 posts

Geek

#369625 19-Aug-2010 00:23

Yes! That's a Prefect solution!!! I'll go about doing that?! Cheers and thank you

Samsung

Shop now on Samsung phones, tablets, TVs and more (affiliate link).

nickd

447 posts

Ultimate Geek

#369627 19-Aug-2010 00:44

This is what NonPrayingMantis was getting at:https://spreadsheets.google.com
Once you have your sheet set up & working, you can just hide columns F->I.

manhinli

2483 posts

Uber Geek

Trusted

#369898 19-Aug-2010 16:50

Ah - so what you wanted was a daily increase in power for each meter.

Why didn't you just say that? :D

But anyway, good luck with your little metering spreadsheet. :)

Find me on Twitter!

I posted 1, 2 x 10^3 times!

Hacktrix

15 posts

Geek

#369972 19-Aug-2010 19:44

Yea should had made my self clearer, truth is I didn't really know what I wanted. Thanks Guys.

21stCentury

4 posts

Wannabe Geek

#454956 4-Apr-2011 06:06

Hello,

I requested that this forum be reopened because it is similar to a spreadsheet I'm trying to develop.

Here goes:

I read a water meter on 1/15/11. Again on 2/20/11 and again on 3/22/11.

I have an Excel spreadsheet with 5 columns.

A.Date
B.Days between readings
C.Meter Reading
D.Gallons used between meter readings
E.Average gallons per day between readings. (gpd)

Simple.

Here's the catch. I need to report monthly usage. I can calculate it manually by Multiplying the average gpd times 15 in January plus the average gpd of the next reading in February times the 16 days left in January.

The formula would look like this: E1*15 + E2*16.

I need a formula that would do this automatically no matter what day I read the meter.

Any Ideas.