average difference without a helper column

  • Thread starter Thread starter david_g
  • Start date Start date
D

david_g

I have a considerable amount of data which is already in excel
A is the date, B is the final meter reading and C is the number of
hours the pump was operating
date meter1 hours
01-Jan-10 0 0
02-Jan-10 222 4
03-Jan-10 1419 2
04-Jan-10 2009 3
05-Jan-10 3081 5
06-Jan-10 4360 10

I want to calculate the average flow per day i.e. b2-b1 through to
b365
similarly the average pump time i.e. c2-c1

Of course there is a way to do this with helper columns (I think that
is the correct term) but I have over 300 excel sheets and hope that
there is an easier way
 
I want to calculate the average flow per day i.e. b2-b1
through to b365
similarly the average pump time i.e. c2-c1
Click to expand...

Both of the following formulas do what you want, I believe. I think
the array formula is more efficient; it also can be easily modified to
compute other statistics (e.g. std dev). But array formulas are error-
prone: sometimes they return a number instead of an error even though
you enter them incorrectly (i.e. press Enter instead of ctrl+shift
+Enter). So you might overlook your mistake.

Normal non-array formula:

=SUMPRODUCT(B2:B365-B1:B364)/COUNT(B1:B364)

Of course, you could use 364 instead of COUNT(B1:B364) if you wish.

Array formula [*]:

=AVERAGE(B2:B365-B1:B364)

[*] Enter an array formula by pressing ctrl+shift+Enter instead of
just Enter. Excel will display an array formula surrounded by curly
braces in the Formula Bar. You cannot type the curly braces
yourself. If you make a mistake, select the cell, press F2 and edit
as needed, then press ctrl+shift+Enter.
 
Back
Top