Average Hours Per Day - Running Total Average

Hi all,

I have a list of daily hours a machine has achieved by date, (the total hours of operation of that machine for each day of it's life). I want to find the average hours that machine is used in a day.
So I need to calculate the differences between two consecutive cells (B2-B1, B3-B2...ect), then find the average of all those differences to give me an estimated hourly use of a machine per day.

I’m trying very hard not to create any additional columns for “working” in my spreadsheet.

My data is

ROW COLUMN A COLUMN B
DATE HOURS
1 01 Oct 12 29,091
2 02 Oct 12 29,111
3 03 Oct 12 29,130
4 04 Oct 12 29,150
5 05 Oct 12 29,172
6 06 Oct 12 29,172
7 07 Oct 12 29,172
8 08 Oct 12 29,172
9 09 Oct 12 29,182
10 10 Oct 12 29,192
11 11 Oct 12 29,208
12 12 Oct 12 29,208
13 13 Oct 12 29,227
14 14 Oct 12 29,248
15 15 Oct 12 29,271
16 16 Oct 12 29,294
17 17 Oct 12 29,315
18 18 Oct 12 29,330
19 19 Oct 12 29,352
20 20 Oct 12 29,365
21 21 Oct 12 29,387
22 22 Oct 12 29,408
23 23 Oct 12 29,430
24 24 Oct 12 29,448
25 25 Oct 12 29,468
26 26 Oct 12 29,486
27 27 Oct 12 29,487
28 28 Oct 12 29,503
29 29 Oct 12 29,505
30 30 Oct 12 29,506
31 31 Oct 12 29,512


Manually I have worked out that this machine works on average 14 hours a day, but in my manual workings I needed to make an extra column to create the difference in values between each day.

Another thing, this list is being updated daily (new daily hours are being entered), so I need the formula to be able to pick up the new entries as they are entered.
I don’t want much do I

If you can help it would be much appreciated.

Control+shift+enter, not just enter:

=AVERAGE(B3:B32-B2:B31)

Hi, Sorry for the confusion

Attached is my data for one machine and it's hours from 30/6/2009 though to the 6/12/2012.
these figures are the hour reader (think a car odometer reading), these hours accumulate overtime the machine is turned on.

How many hours on average per day did the machine work for?

how can I turn that formula {=AVERAGE(B3:B32-B2:B31} into an "open ended formula" (for want of a better phrase) so that any new entered data is captured.
Each day the spreadsheet is updated with new hours for the machine.

Hi,

I've updated my spreadsheet.
I've added a small table, this is the Report I'm trying to create, please try to ignore all the other cells except the yellow one but it might help you understand what I'm trying to do.
So for a machine I am estimating the service intervals required for that machine. i.e. my machine needs a service every 250 hours, i can work out when the next service is due, roughly the date that that service is due on, what the hour meter will read when the service is due.

In my yellow cell (K7), I want the formula to work out a)the newest entry-earliest entry, then average that
As at 6/12/2012=(Earliest entry-first entry/count of nonblank cells) OR(B1257-B2/1256) = 14.3 hours (this machine operates for an average of 14.3 hours a day)


HOWEVER on the next day 7/12/2012, a new entry will be added to row 1258, so I need my formula to add this in and adjust the average...... does that make any sense at all!!

Hi everyone I have the exact problem but it does not solve what I am hoping to achieve.

I know there is a lot of bright minds on this forum and I know you can solve my problem.

I have attached a document so you guys can see what I am hoping to achieve.

Wow so you speak Afrikaans Dit gaan goed hier in Pongola dankie

I have opened a new thread like you suggested and I just want to thank you for giving me advise as I am new to this forum.

The new thread link can be found here

http://www.excelforum.com/excel-form...ml#post3611949

Kind Regards,
Wessel