XL2000 - Average/If/And Functions

[LPS]

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('Evaluation
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]

P.S.: When I copy the function to another instructor's average cell, I do
change the name of the instructor.

 

[Bob Phillips]

=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]

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?

 

[Bob Phillips]

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.

 

[LPS]

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]

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

 

[Bob Phillips]

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,