XL2000 Averaging Problems Continue...

[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), Column A is Instructor Name,
Column B is the date in MMM-YY format, C is number of courses, D is number of
evaluations and E is Evaluation Average. In column E I want to calculate
the evaluation average per instructor, per month.

I have tried the following calculation but it does not return anything. I
have looked at it until my eyes are crossed but I cannot find why it is not
working. I think something is missing but... what???

Can anyone shed some much needed light on this? I appreciate all suggestions.

=IF(ISERROR(SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A7),--(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=A7),--(TEXT('Evaluation
Detail'!D$7$500,"mmm-yy")=TEXT(B1,"mmm-yy")))),"",SUMPRODUCT(--('Evaluation
Detail'!$F$7:$F$500=A7),--(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=A7),--(TEXT('Evaluation
Detail'!D$7$500,"mmm-yy")=TEXT(B1,"mmm-yy"))))

 

[bpeltzer]

First question: what is B1? My immediate thought is either that this should
be B7 OR B$1 (if you want to reference a fixed row).
Second: you said that the dates were formatted as Mmm-yy. Are they simply
displayed that way? Or are then entered as text in that format? The
comparison you are making is to text in that format, not to actual dates! If
the data is really dates, then you can (in fact must) compare to dates,
without the text( ) conversion; the different formatting affects only the
display, not the cell's value.

 

[Dave Peterson]

If you're formula is evaluating to "", it could be because there are errors in
any of the cells in any of the input range--or because the denominator is equal
to 0.

I'd split the long formula (just the "else" portion) into multiple cells. One
cell for the numerator and one cell for the denominator.

If the denominator is 0 (#div/0! error) and you "know" that it shouldn't be,
it's time to check the input to see if there are really any matches.

If there's a different error, I'd look for errors (n/a's, ref's, etc) in those
ranges.

 

[ShaneDevenshire]

Hi,

as already stated, we need to know what is actually in cell B1and D7500.

It looks to me like your working way to hard but I need to know details.
For example if B1 and D7500 are entered as dates in any legal excel format
then the TEXT function is unnecessary. Since you are re-referencing the same
range multiple times and it's on another sheet, you should definitely
consider range names.

 

[LPS]

Thank you all for your feedback. When you look at something for too long,
you stop "seeing" what is there. My date comparisons (in Column B on the
Summary sheet and Column D on the Detail sheet) are comparing excel dates
(derived from =date(yyy,mm,dd). The problem seems to stem from incorrect
cell referencs - B1 should have been B7 (date field). I fixed that and also
made the columns absolute and now things seem to be working. Also, the idea
of using named ranges makes much more sense... wish I had thought of that!
You learn as you go and I appreciate all the help and knowledge you have
passed on to me. Now I know about this.

Until the next time... Thank You!