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!

Calculate average based on date and other criteria
 

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" wrote in message
...
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!

Calculate average based on date and other criteria
 

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" wrote in message
...
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!

Calculate average based on date and other criteria
 

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" wrote in message
...
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" wrote in message
...
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!