formula for daily consumption

[jv]

Hi,

I would like to ask your help if could make a formula for
the daily consumption.
A B C D E
Date Main Supply AKC HAK AFH
01-Mar 233821 946948 663258 584855
02-Mar 233883 947189 663381 585147
03-Mar 233948 947449 663475 585439
04-Mar

Consump. 127000 501000 217000 584000

From the above sample sheet, readings are being inputted
in column B, C, D, E in a daily basis for the whole month.
Resulting daily consumption should appeared below.
Computation will be from the present date readings - 1st
date reading x 1000.

Daily consumption will change everyday as reading are
inputted daily.

Can it be done?


jv

 

[Frank Kabel]

Hi
try the following for your consumption formulas (assumption: These
totals are in different columns):
=SUM(OFFSET($B$2,0,0,COUNTA($B$2:$B$1000))*1000
for column B consumption

 

[Norman Harker]

Hi jv!

With dates in A2:A32 and readings in B2:B32, I get:

Total consumption:
=(MAX(B2:B32)-MIN(B2:B32))*1000
Average consumption:
=(MAX(B2:B32)-MIN(B2:B32))*1000/COUNTA(B2:B32)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[jv]

Frank,

I got a "#value" result,i dont know much what the formula
means but my sheet is formatted in number.

=SUM(OFFSET($B$2,0,0,COUNTA($B$2:$B$1000))*1000

What i would like to have is a computation for daily
consumption based on the daily readings for different
columns.

Let say that I have reading today 05-Mar.(present reading)
will be deducted always from 01-Mar.reading to get the
difference then multiplied by 1000 to get the KWH reading.
A B
ex. 01-Mar. 233821
02-Mar.
03-Mar.
04-Mar.
05-Mar. 234012

Consump. 191000
(b5-b1)*1000
present date - 01-Mar. reading * 1000


Hope you find a way to make this possible.

Regards,

jv

 

[Norman Harker]

Hi jv!

I tested my solution and it seems to work. Total consumption for the
columns is as per your calculations. Average is based on the number of
readings but I think requires correction to:

=(MAX(B2:B32)-MIN(B2:B32))*1000/(COUNTA(B2:B32)-1)

I need to deduct 1. It's that darned fence panels and fence posts
problem again.

I've assumed that these are meter readings and that the latest reading
will always be the highest.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[jv]

Norman,

Thanks for your reply. ill apply your tip tomorrow when im
in office.

Regards,

jv