MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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