Excel formula

Posted by Charles on January 18, 2002 9:27 AM

I am averaging grades that have various category headings using this formula.
=(AVERAGE(IF(C$28:V$28="WEX 121",C32:V32)))
I need to tell Excel that if one of the grades for a student is missing to not calculate it in with his/her average for this category. I would like to be able to put an NC or something like this in the cell and just have the formula skip the students grade in that cell. How can I edit this formula or write another to accomplish this.

Posted by Aladin Akyurek on January 18, 2002 9:51 AM

Charles --

Not exactly sure about the lay-out of your data, but try:

=AVERAGE(IF((C$28:V$28="WEX 121")*(C32:V32),C32:V32))

to be array-entered by hitting control+shift+enter at the same time, instead of just enter.

If this not what you want, please post 10 rows of your data in the follow-up.

Aladin

=======

Posted by Charles on January 18, 2002 11:25 AM

Yes thank you very much! :) I have found one more problem. If I have only one catagory and the student does not have a grade entered my final total average does not work. I was using this simple formula =AVERAGE(W33:AB33) This is my cell range where my catagory averages were calculated. Do you have any ideas about this one. Again thanks so much. Setting this up is really getting on my nerves.

Posted by Aladin Akyurek on January 18, 2002 12:55 PM

> I have found one more problem. If I have only one catagory and the student does not have a grade entered my final total average does not work.

Charles: You mean you get a #DIV/0! as result of the array-formula, right? If so, you can use one of:

[1] =SUM(IF((C$28:V$28="WEX 121")*(C32:V32),C32:V32))/MAX(1,COUNTIF(C32:V32,">0"))

still to be array-entered

[2] =SUMPRODUCT((C$28:V$28="WEX 121")*(C32:V32),C32:V32)/MAX(1,COUNTIF(C32:V32,">0"))

wchich you enter as an ordinary formula.

Select the cell(s) of the formula that you use and custom format as:

[0]"";General

Aladin

=======

Posted by Charles on January 18, 2002 2:09 PM

The catagory grades are working. You fixed that for me. Thanks. If there is more than one task of the same kind and a student has a blank for one of them the average comes out right for those he/she did take. But, my final average which is an average of all my catagories will read the zero that is given as the result of a single blank catagory grade and average it in the final grade.
Say there was only one task given for WEX 130 and this student didn't get a chance to perform the task. Then this happens to his final grade.

Cat. Cat. Cat.
WEX 120 WEX 121 WEX 130 FINAL
100 100 0 66.7

Posted by Aladin Akyurek on January 18, 2002 2:15 PM

Cat. Cat. Cat.

And you're using

AVERAGE(W33:AB33)

to compute the FINAL, as you indeed said in the previous post.

You want to ignore the zero's here. If so, use:

=SUM(W3:AB33)/MAX(1,COUNTIF(W3:AB33,">0"))

Right?

Aladin

Posted by Charles on January 18, 2002 2:23 PM

Yesssss. Thank you!! That has done it for me. Now I can go home for the weekend. Thanks again, You are an Excel God.

Posted by Aladin Akyurek on January 22, 2002 3:33 AM