Averaging Time
 

Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.

Averaging Time
 

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

Averaging Time
 

Hi,

Extract the time into a helper column with

=TIME(HOUR(A1),MINUTE(A1),0)

Drag down as required and average these and you should get 09:27

Mike

"BostonBoy" wrote:

Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.

Averaging Time
 

First of all, I'm sorry for the multiple posts. My browser kept showing
error messages upon hitting the send button.

I tried the method you suggested and received the #value! error message.

The original cells are formatted as date cells. I've changed their format
to time cells, but that isn't working either. Any other ideas on this one?
I've got daily entries for 2 years, so copying them into a new space is time
consuming


"Glenn" wrote:

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

Averaging Time
 

Got it!

"Glenn" wrote:

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

Averaging Time
 

Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.

"Glenn" wrote:

BostonBoy wrote:
Were trying to figure our how early an employee typically punches in for
work, as we want to give her a bonus. I have the punch clock data in an
excel column in the following format (a few of the entries from this past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

Shes supposed to start work at 10:00am every day, and I need to figure out
what time she usually checks in. Ive tried a typical averaging formula, but
that gives me the mean time between all of the days, which is around noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes. I've
been having trouble with the log-on for the last hour.


With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

Averaging Time
 

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically punches in
for
work, as we want to give her a bonus. I have the punch clock data in
an
excel column in the following format (a few of the entries from this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes.
I've
been having trouble with the log-on for the last hour.

Averaging Time
 

Didn't work for me. The formula seems affested by the empty cells in between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically punches in
for
work, as we want to give her a bonus. I have the punch clock data in
an
excel column in the following format (a few of the entries from this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of minutes.
I've
been having trouble with the log-on for the last hour.

Averaging Time
 

I've got the 5 values from your original example. I average them with the
formula which Glenn suggested, & get 10:02. If I include blank lines so
that the formula now calls up a range of 9 cells, 4 of them blank, the
average is stll 10:02.

What values do you have, what formula, what result did you get, and what
result did you expect?
--
David Biddulph

"BostonBoy" wrote in message
...
Didn't work for me. The formula seems affested by the empty cells in
between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the
entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically punches
in
for
work, as we want to give her a bonus. I have the punch clock data
in
an
excel column in the following format (a few of the entries from this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to
figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of
minutes.
I've
been having trouble with the log-on for the last hour.

Averaging Time
 

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

The average of those times is 9:27. It can't be 10:02, because that's the
highest one. Perhaps you're having the same problem as me. When I add rows
in between the numbers, the ultimate average changes. Very perplexing.

If I delete the empty rows, it works. But I've got hundreds of them, and
I'm hoping there's a formula that can do it. Any other ideas?



The five values from the original example were

"David Biddulph" wrote:

I've got the 5 values from your original example. I average them with the
formula which Glenn suggested, & get 10:02. If I include blank lines so
that the formula now calls up a range of 9 cells, 4 of them blank, the
average is stll 10:02.

What values do you have, what formula, what result did you get, and what
result did you expect?
--
David Biddulph

"BostonBoy" wrote in message
...
Didn't work for me. The formula seems affested by the empty cells in
between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the
entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically punches
in
for
work, as we want to give her a bonus. I have the punch clock data
in
an
excel column in the following format (a few of the entries from this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to
figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of
minutes.
I've
been having trouble with the log-on for the last hour.

Averaging Time
 

Yes, Sorry. I must have been asleep. I fell into the old trap of
forgetting to array-enter the formula. When I enter the formula correctly I
see the problem as you describe.
To get a correct answer I used a helper column with =IF(A2<"",MOD(A2,1),"")
and averaged that.
--
David Biddulph

"BostonBoy" wrote in message
...

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

The average of those times is 9:27. It can't be 10:02, because that's the
highest one. Perhaps you're having the same problem as me. When I add
rows
in between the numbers, the ultimate average changes. Very perplexing.

If I delete the empty rows, it works. But I've got hundreds of them, and
I'm hoping there's a formula that can do it. Any other ideas?



The five values from the original example were

"David Biddulph" wrote:

I've got the 5 values from your original example. I average them with
the
formula which Glenn suggested, & get 10:02. If I include blank lines so
that the formula now calls up a range of 9 cells, 4 of them blank, the
average is stll 10:02.

What values do you have, what formula, what result did you get, and what
result did you expect?
--
David Biddulph

"BostonBoy" wrote in message
...
Didn't work for me. The formula seems affested by the empty cells in
between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the
entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically
punches
in
for
work, as we want to give her a bonus. I have the punch clock
data
in
an
excel column in the following format (a few of the entries from
this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to
figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is
around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of
minutes.
I've
been having trouble with the log-on for the last hour.

Averaging Time
 

I tried making the helper column, but if I drag the formula down the entire
helper column, then all the rows where there was no entry in A get a
"12:00am" entry, because the formula in the B helper column is acting against
a blank in the A column. So, my ultimate averaging at the bottom comes up
skewed. Any way to make a helper column without deleting every row with no
entry?

"David Biddulph" wrote:

Yes, Sorry. I must have been asleep. I fell into the old trap of
forgetting to array-enter the formula. When I enter the formula correctly I
see the problem as you describe.
To get a correct answer I used a helper column with =IF(A2<"",MOD(A2,1),"")
and averaged that.
--
David Biddulph

"BostonBoy" wrote in message
...

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

The average of those times is 9:27. It can't be 10:02, because that's the
highest one. Perhaps you're having the same problem as me. When I add
rows
in between the numbers, the ultimate average changes. Very perplexing.

If I delete the empty rows, it works. But I've got hundreds of them, and
I'm hoping there's a formula that can do it. Any other ideas?



The five values from the original example were

"David Biddulph" wrote:

I've got the 5 values from your original example. I average them with
the
formula which Glenn suggested, & get 10:02. If I include blank lines so
that the formula now calls up a range of 9 cells, 4 of them blank, the
average is stll 10:02.

What values do you have, what formula, what result did you get, and what
result did you expect?
--
David Biddulph

"BostonBoy" wrote in message
...
Didn't work for me. The formula seems affested by the empty cells in
between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the
entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically
punches
in
for
work, as we want to give her a bonus. I have the punch clock
data
in
an
excel column in the following format (a few of the entries from
this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need to
figure
out
what time she usually checks in. I've tried a typical averaging
formula, but
that gives me the mean time between all of the days, which is
around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of
minutes.
I've
been having trouble with the log-on for the last hour.

Averaging Time
 

BostonBoy wrote:
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.


=AVERAGE(IF(A1:A999="","",MOD(A1:A999,1)))

Again, array-entered. Adjust the range as needed.

Averaging Time
 

Genius! Thanks, Phil

"Glenn" wrote:

BostonBoy wrote:
Any way to make that formula work with empty rows in between the entries?
I've got a couple hundred of them.


=AVERAGE(IF(A1:A999="","",MOD(A1:A999,1)))

Again, array-entered. Adjust the range as needed.

Averaging Time
 

If =IF(A2<"",MOD(A2,1),"") is returning a zero, then column A isn't blank.
--
David Biddulph

"BostonBoy" wrote in message
...
I tried making the helper column, but if I drag the formula down the entire
helper column, then all the rows where there was no entry in A get a
"12:00am" entry, because the formula in the B helper column is acting
against
a blank in the A column. So, my ultimate averaging at the bottom comes up
skewed. Any way to make a helper column without deleting every row with
no
entry?

"David Biddulph" wrote:

Yes, Sorry. I must have been asleep. I fell into the old trap of
forgetting to array-enter the formula. When I enter the formula
correctly I
see the problem as you describe.
To get a correct answer I used a helper column with
=IF(A2<"",MOD(A2,1),"")
and averaged that.
--
David Biddulph

"BostonBoy" wrote in message
...

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

The average of those times is 9:27. It can't be 10:02, because that's
the
highest one. Perhaps you're having the same problem as me. When I add
rows
in between the numbers, the ultimate average changes. Very perplexing.

If I delete the empty rows, it works. But I've got hundreds of them,
and
I'm hoping there's a formula that can do it. Any other ideas?



The five values from the original example were

"David Biddulph" wrote:

I've got the 5 values from your original example. I average them with
the
formula which Glenn suggested, & get 10:02. If I include blank lines
so
that the formula now calls up a range of 9 cells, 4 of them blank, the
average is stll 10:02.

What values do you have, what formula, what result did you get, and
what
result did you expect?
--
David Biddulph

"BostonBoy" wrote in message
...
Didn't work for me. The formula seems affested by the empty cells
in
between
the numbers. Any ideas?

"David Biddulph" wrote:

Not a problem if there are empty rows. The formula still works.
--
David Biddulph

"BostonBoy" wrote in message
...
Any way to make that formula work with empty rows in between the
entries?
I've got a couple hundred of them.

"Glenn" wrote:
With your data in A1:A5, put the following in B1:

=AVERAGE(MOD(A1:A5,1))

and hit Ctrl-Shift-Enter (because this is an array formula).

Format B1 as time.

BostonBoy wrote:
We're trying to figure our how early an employee typically
punches
in
for
work, as we want to give her a bonus. I have the punch clock
data
in
an
excel column in the following format (a few of the entries
from
this
past
month):

5/14/08 10:02 AM
5/15/08 9:16 AM
5/17/08 9:28 AM
5/18/08 9:16 AM
5/22/08 9:15 AM

She's supposed to start work at 10:00am every day, and I need
to
figure
out
what time she usually checks in. I've tried a typical
averaging
formula, but
that gives me the mean time between all of the days, which is
around
noon on
the 17th.

Any ideas?

Sorry if this is a duplicate post by me in the last couple of
minutes.
I've
been having trouble with the log-on for the last hour.