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