I am trying to average time for multiple entries over a year. I have about
10,000 records with the date and a total time. On my next sheet I have the
start date and end date for each week of that year columns A&B respectivly.
The formula below returns a result but it is wrong. I ran an average for the
first week to conform it and it is off by more than 20 seconds. Can anyone
show me what I did wrong. Thank you

{=AVERAGE(IF(Sheet1!A$2:A$5310=Sheet2!A2,IF(Sheet 1!B$2:B$5310<=Sheet2!B2,Sheet1!I$2:I$5310)))}

Looks fine, but maybe try this variation

=AVERAGE(IF((Sheet1!A$2:A$5310=Sheet2!A2)*(Sheet1 !B$2:B$5310<=Sheet2!B2),Sh
eet1!I$2:I$5310))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jason" wrote in message
...
I am trying to average time for multiple entries over a year. I have about
10,000 records with the date and a total time. On my next sheet I have the
start date and end date for each week of that year columns A&B
respectivly.
The formula below returns a result but it is wrong. I ran an average for
the
first week to conform it and it is off by more than 20 seconds. Can anyone
show me what I did wrong. Thank you


{=AVERAGE(IF(Sheet1!A$2:A$5310=Sheet2!A2,IF(Sheet 1!B$2:B$5310<=Sheet2!B2,Sh
eet1!I$2:I$5310)))}

Try this amendment to your formula:

=AVERAGE(IF((Sheet1!A$2:A$5310=Sheet2!A2)*(Sheet1 !B$2:B$5310<=Sheet2!B2),Sheet1!I$2:I$5310))

As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTR-SHIFT-ENTER instead of just
ENTER. If you do this correctly, Excel will wrap curly braces { }
around the formula - you must not type these yourself.

Hope this helps.

Pete

Jason wrote:
I am trying to average time for multiple entries over a year. I have about
10,000 records with the date and a total time. On my next sheet I have the
start date and end date for each week of that year columns A&B respectivly.
The formula below returns a result but it is wrong. I ran an average for the
first week to conform it and it is off by more than 20 seconds. Can anyone
show me what I did wrong. Thank you

{=AVERAGE(IF(Sheet1!A$2:A$5310=Sheet2!A2,IF(Sheet 1!B$2:B$5310<=Sheet2!B2,Sheet1!I$2:I$5310)))}

Oh, I see Bob beat me to it - he types too fast !! <g

Pete

Pete_UK wrote:
Try this amendment to your formula:

=AVERAGE(IF((Sheet1!A$2:A$5310=Sheet2!A2)*(Sheet1 !B$2:B$5310<=Sheet2!B2),Sheet1!I$2:I$5310))

As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTR-SHIFT-ENTER instead of just
ENTER. If you do this correctly, Excel will wrap curly braces { }
around the formula - you must not type these yourself.

Hope this helps.

Pete

Jason wrote:
I am trying to average time for multiple entries over a year. I have about
10,000 records with the date and a total time. On my next sheet I have the
start date and end date for each week of that year columns A&B respectivly.
The formula below returns a result but it is wrong. I ran an average for the
first week to conform it and it is off by more than 20 seconds. Can anyone
show me what I did wrong. Thank you

{=AVERAGE(IF(Sheet1!A$2:A$5310=Sheet2!A2,IF(Sheet 1!B$2:B$5310<=Sheet2!B2,Sheet1!I$2:I$5310)))}

Thank you I tried it and I got the same result as my orginal formula. When I
run just a staight average of the first weeks numbers I get 6:26 when I put
yours and my formula in I get 6:13. I am at a lose. Jay

"Bob Phillips" wrote:

Looks fine, but maybe try this variation

=AVERAGE(IF((Sheet1!A$2:A$5310=Sheet2!A2)*(Sheet1 !B$2:B$5310<=Sheet2!B2),Sh
eet1!I$2:I$5310))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jason" wrote in message
...
I am trying to average time for multiple entries over a year. I have about
10,000 records with the date and a total time. On my next sheet I have the
start date and end date for each week of that year columns A&B
respectivly.
The formula below returns a result but it is wrong. I ran an average for
the
first week to conform it and it is off by more than 20 seconds. Can anyone
show me what I did wrong. Thank you


{=AVERAGE(IF(Sheet1!A$2:A$5310=Sheet2!A2,IF(Sheet 1!B$2:B$5310<=Sheet2!B2,Sh
eet1!I$2:I$5310)))}

Didn't help though :-)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pete_UK" wrote in message
ups.com...
Oh, I see Bob beat me to it - he types too fast !! <g

Pete

Pete_UK wrote:
Try this amendment to your formula:


=AVERAGE(IF((Sheet1!A$2:A$5310=Sheet2!A2)*(Sheet1 !B$2:B$5310<=Sheet2!B2),Sh
eet1!I$2:I$5310))

As this is an array formula then once you have typed it in (or
subsequently edit it) you must use CTR-SHIFT-ENTER instead of just
ENTER. If you do this correctly, Excel will wrap curly braces { }
around the formula - you must not type these yourself.

Hope this helps.

Pete

Jason wrote:
I am trying to average time for multiple entries over a year. I have
about
10,000 records with the date and a total time. On my next sheet I have
the
start date and end date for each week of that year columns A&B
respectivly.
The formula below returns a result but it is wrong. I ran an average
for the
first week to conform it and it is off by more than 20 seconds. Can
anyone
show me what I did wrong. Thank you


{=AVERAGE(IF(Sheet1!A$2:A$5310=Sheet2!A2,IF(Sheet 1!B$2:B$5310<=Sheet2!B2,Sh
eet1!I$2:I$5310)))}