Calculate average based on date and other criteria

[Kycajun]

I have a worksheet (sheet 1) which include the following columns:
Audit Date.... Name.... File Score...
(A3:A9949) (D39949) (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!

 

[Biff]

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")*(D39949="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)*(D39949=N1),L3:L9949))

Biff

 

[Kycajun]

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]

Ooops!

I goofed.

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

Should be:

=AVERAGE(IF((A3:A9949>=--"2006/7/1")*(D39949="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)*(D39949=N1),L3:L9949))

Biff