There was a small bug in the formula that I didn't change from your
original, it should be

=AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1))
*('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30))
*('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation
Detail'!$Q$7:$Q$499))

still array entered.

What does this mean ... When I copy it to another instructor's average cell,
and change the instructor's name in the function,

--
__________________________________
HTH

Bob

"LPS" wrote in message
...
I am using this function, --

=AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1))
*('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30))
*('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation
Detail'!$Q$7:$Q$499,0))

However, if is not working. When I copy it to another instructor's
average
cell, and change the instructor's name in the function, it still
calculates
the same result, regardless of whether or not the instructor's name even
exists in the source data.


LPS


"LPS" wrote:

Hi again. Thank you again for trying to make it easy for me. I tried
your
suggestion and even with using the array formula (ctrl+shift+enter), it
gives
incorrect results. I just don't get it.

I was using the "number not equal to 0" as a way of eiliminating from the
calcuation, cells that were blank, as zero values can sqew averages.

I don't know what to try next.... any suggestions?
--
LPS


"Bob Phillips" wrote:

That is exactly what that formula is doing. The * are effectively AND
statements here, Dates GT 1st Sep AND Dates <= 30th Sep AND instructors
=
"Thaddeus Thomas", by virtue of the way that the conditions are
evaluated.

Looking at it agian, the test for the numbere not 0 is superfluous, a
number
+ 0 is the same number, so it is only (sic!)

=AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1))
*('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30))
*('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas"),'Evaluation
Detail'!$Q$7:$Q$499,0))

The curly brackets are added by Excel when you do Ctrl-Shift_Enter,
they are
not added by you. As I said you MUST Ctrl-Shift_Enter the formula, and
if
you edit it, you must Ctrl-Shift_Enter again. It will return an
incorrect
answer if you just Enter it.

--
__________________________________
HTH

Bob

"LPS" wrote in message
...
Thank you for your suggestion, Bob... I tried it but it returns an
incorrect
value and I do not understand what it is doing. Cannot I not just
enter a
formula which says, if the month is September and the instructor is
Thaddeus
Thomas, average his course evaluations?

In an array function, what do the asterisk and the squiggly brackets
mean/do? Are there any reference materials I can access?

--
LPS


"Bob Phillips" wrote:

=AVERAGE(IF(('Evaluation Detail'!$D$7:$D$499=DATE(2008,9,1))
*('Evaluation Detail'!$D$7:$D$499<=DATE(2008,9,30))
*('Evaluation Detail'!$F$7:$F$499="Thaddeus Thomas")
*('Evaluation Detail'!$Q$7:$Q$499<0),'Evaluation
Detail'!$Q$7:$Q$499,0))

You MUST array enter it, it won't work otherwise.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"LPS" wrote in message
...
My Excel 2000 workbook has two sheets, the first (Evaluation
Detail)
contains
details about course evalutions per instructor. Column A is
Course
Name,
B
and C are Course Numbers, D is Course Date, E is Training
Provider, F
is
Instuctor Name, G thru P are evaluation scores and Q is Evaluation
Average.

On the second sheet (Evaluation Summary) I want to calculate the
overall
course average per instructor, per month. I have written an
AVERAGE
statement which calls an IF/AND statement (see below). It works
but
returns
the same results for each instructor. When I manually calculate
the
averages
per instructor per date,they are not the same. Can anyone advise
as to
why
my function is not working. I tried making it an array function
(CTRL+SHIFT+ENTER) but it caused the results to return "0" (and I
don't
really understand array functions - :( )!

=AVERAGE(IF(AND('Evaluation
Detail'!$D$7:$D$499=DATE(2008,9,1),'Evaluation
Detail'!$D$7:$D$499<=DATE(2008,9,30)),IF('Evaluati on
Detail'!$F$7:$F$499="Thaddeus Thomas",IF('Evaluation
Detail'!$Q$7:$Q$499<0,'Evaluation Detail'!$Q$7:$Q$499,0))))

I greatly appreciate any and all help.
--
LPS