New Twist on Old Question-Averages

[LPS]

Refresher: 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.

Although this is not exactly the solution that was suggested to me by some
very helpful peolpe (I mean that) I finally found a function which will
calculate the averages I need, and if the calculation returns a "#DIV/0!"
error, to display the cell as blank. What I now need to do is add in the
date selection component, so that evaluation averages are only calculated for
a specific month, e.g.; >= 1-Sept-08 and <= 30-Sep-08. I have tried to do
this with dismal results. Can anyone help me with the correct syntax? The
following is the calculation as it currently works:

=IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500="Linda
Sgabellone"),'Evaluation Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation
Detail'!$F$7:$F$500,"Linda Sgabellone")),"",SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500="Linda Sgabellone"),'Evaluation
Detail'!$Q$7:$Q$500)/COUNTIF('Evaluation Detail'!$F$7:$F$500,"Linda
Sgabellone"))

Thank you for all of your help -

 

[Pete_UK]

SUMPRODUCT can be used for conditional summing and for conditional
counting, (with more than one condition, unlike COUNTIF), so you can
set up the conditions that you mention. I would favour having a cell
in which you can enter a date like 1st Sept 2008 (although the day
doesn't matter), and then your condition will be:

(TEXT(D$7$500,"mmm-yy")=TEXT(cell,"mmm-yy"))

to check for the month and year.

Unfortunately I have to go out now, but I'll complete this later (if
no-one else does).

Hope this helps for now,

Pete

 

[Pete_UK]

Okay, try this:

=IF(ISERROR(SUMPRODUCT(--('Evaluation Detail'!$F$7:$F$500=A1),--
(TEXT('Evaluation Detail'D$7$500,"mmm-yy")=TEXT(B1,"mmm-
yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7$500,"mmm-
yy")=TEXT(B1,"mmm-yy")))),"",SUMPRODUCT(--('Evaluation Detail'!$F$7:$F
$500=A1),--(TEXT('Evaluation Detail'D$7$500,"mmm-yy")=TEXT(B1,"mmm-
yy")),'Evaluation Detail'!$Q$7:$Q$500)/SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A1),--(TEXT('Evaluation Detail'D$7$500,"mmm-
yy")=TEXT(B1,"mmm-yy"))))

where A1 is used to enter the name of the person of interest (eg
"Linda Sgabellone", without the quotes), and B1 is the date I
mentioned earlier, eg 1st Sept 2008.

An alternative would be to use AVERAGE(IF ... as an array formula,
but I don't have time now to see if that will be significantly
shorter.

Hope this helps.

Pete

 

[LPS]

Hi Pete. I can see you have put a lot of thought into this for me and I
really appreciate it. I will have to play with this to see if I (a)
understand it and (b) can make it work.

Thanks so much. I will post a note to let you (and others who may be
interested in a similar solution) know if I was successful.

Cheers,

 

[Pete_UK]

Okay, feedback is always appreciated.

Pete

Hi Pete.  I can see you have put a lot of thought into this for me and I
really appreciate it.  I will have to play with this to see if I (a)
understand it and (b) can make it work.

Thanks so much.  I will post a note to let you (and others who may be
interested in a similar solution) know if I was successful.

Cheers,
--
LPS









- Show quoted text -