Catagory averages are effecting each other , What to do?
Posted by Charles on January 18, 2002 4:31 PM
I am using this formula.
=SUM(IF((C$28:V$28="WEX 121")*(C32:V32),C32:V32))/MAX(1,COUNTIF(C32:V32,">0"))
I have several single grades that are averaged into various catagory grades that are then averaged into a final grade.
The above formula is alowing my catagory grades to effect each other.
If I have a score of 100 in a single catagory called WEX everything is OK, but when a second catagory WEL gets an average of 50 they effect each other. WEX drops to 50 and WEL shows up as 25. Does anyone know how I can fix this?
Posted by Aladin Akyurek on January 18, 2002 5:16 PM
Charles --
You seem thinking about a sort of weighted average in mind. Do you have one?
Aladin
==========
Posted by CHarles on January 18, 2002 7:24 PM
No, no wieghting all are equal. I am emailing you my chart so you can get a better idea of what I'm trying to do and what the formulas are doing.
Posted by Charles on January 18, 2002 7:30 PM
No weights all are equal
No, no weighting, all are equal. I am emailing you my chart so you can get a better idea of what I'm trying to do and what the formulas are doing.
Posted by Aladin Akyurek on January 22, 2002 3:30 AM
Re: No weights all are equal
The formula for averaging, given at 16204a.html,
needs to be changed to (taking up here the SUMPRODUCT version):
=IF(COUNT(C11:V11),SUMPRODUCT((C$7:V$7=W$7)*(ISNUMBER(C11:V11)),C11:V11)/MAX(1,SUMPRODUCT((C$7:V$7=W$7)*(ISNUMBER(C11:V11)))),0)
W$7's domain consists of different category values such as "WEX 121"and averaging must be done per category (whence the modification in the denominator).
The array version is of coourse similar: just replace SUMPRODUCT with SUM in the above formula and array-enter the resulting formula.
Aladin
