Calculate average based on date and other criteria

I have a worksheet (sheet 1) which include the following columns:
Audit Date.... Name.... File Score...
(A3:A9949) (D3:D9949) (L3:L9949)

On the next worksheet (sheet 2), I want to calculate a file score average
for all entries belonging to a certain name and on or after a certain date
shown on sheet 1. So for instance I want to calculate a file score average
for Doe, John for all Audit Date entries on or after 7/1/2006.

Any suggestions? Thanks!

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

How to enter an array formula:

http://cpearson.com/excel/array.htm

=AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))

Better if you use cells to hold the criteria:

M1 = 7/1/2006
N1 = Doe, John

=AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))

Biff

"Kycajun" <[email protected]> wrote in message
news:[email protected]...
>I have a worksheet (sheet 1) which include the following columns:
> Audit Date.... Name.... File Score...
> (A3:A9949) (D3:D9949) (L3:L9949)
>
> On the next worksheet (sheet 2), I want to calculate a file score average
> for all entries belonging to a certain name and on or after a certain date
> shown on sheet 1. So for instance I want to calculate a file score
> average
> for Doe, John for all Audit Date entries on or after 7/1/2006.
>
> Any suggestions? Thanks!

Thanks! I am working on it now, so far it is not working, but I think I am
close by using your formula. This may sound like a silly question, but how
do you get the two dashes after the equal sign before the date. I tried the
dash key and I don't think it is right, because they don't appear exactly as
your does. Any idea?

"Biff" wrote:

> Hi!
>
> Try this:
>
> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
>
> How to enter an array formula:
>
> http://cpearson.com/excel/array.htm
>
> =AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))
>
> Better if you use cells to hold the criteria:
>
> M1 = 7/1/2006
> N1 = Doe, John
>
> =AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))
>
> Biff
>
> "Kycajun" <[email protected]> wrote in message
> news:[email protected]...
> >I have a worksheet (sheet 1) which include the following columns:
> > Audit Date.... Name.... File Score...
> > (A3:A9949) (D3:D9949) (L3:L9949)
> >
> > On the next worksheet (sheet 2), I want to calculate a file score average
> > for all entries belonging to a certain name and on or after a certain date
> > shown on sheet 1. So for instance I want to calculate a file score
> > average
> > for Doe, John for all Audit Date entries on or after 7/1/2006.
> >
> > Any suggestions? Thanks!
>
>
>

Ooops!

I goofed.

>=AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))

Should be:

=AVERAGE(IF((A3:A9949>=--"2006/7/1")*(D3:D9949="Doe, John"),L3:L9949))

>how do you get the two dashes
>I tried the dash key

Those are just 2 consecutive minus signs. If you use cells to hold the
criteria then you don't have to worry about that.

M1 = 7/1/2006
N1 = Doe, John

=AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))

Biff

"Kycajun" <[email protected]> wrote in message
news:[email protected]...
> Thanks! I am working on it now, so far it is not working, but I think I
> am
> close by using your formula. This may sound like a silly question, but
> how
> do you get the two dashes after the equal sign before the date. I tried
> the
> dash key and I don't think it is right, because they don't appear exactly
> as
> your does. Any idea?
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this:
>>
>> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
>>
>> How to enter an array formula:
>>
>> http://cpearson.com/excel/array.htm
>>
>> =AVERAGE(IF((A3:A9949>=--"2006,7,1")*(D3:D9949="Doe, John"),L3:L9949))
>>
>> Better if you use cells to hold the criteria:
>>
>> M1 = 7/1/2006
>> N1 = Doe, John
>>
>> =AVERAGE(IF((A3:A9949>=M1)*(D3:D9949=N1),L3:L9949))
>>
>> Biff
>>
>> "Kycajun" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a worksheet (sheet 1) which include the following columns:
>> > Audit Date.... Name.... File Score...
>> > (A3:A9949) (D3:D9949) (L3:L9949)
>> >
>> > On the next worksheet (sheet 2), I want to calculate a file score
>> > average
>> > for all entries belonging to a certain name and on or after a certain
>> > date
>> > shown on sheet 1. So for instance I want to calculate a file score
>> > average
>> > for Doe, John for all Audit Date entries on or after 7/1/2006.
>> >
>> > Any suggestions? Thanks!
>>
>>
>>