Excel 2007 : How do I streamline this?

[ChrisLehrich]

The following formula works, but it's ugly -- there MUST be a better way, but I can't figure it out. I'll break it out like a program:

=IFERROR(

SUM(J19,N19,R19,V19,Z19)
/
SUM(

IF(COUNTA(H19)>0,J$2,0),
IF(COUNTA(L19)>0,N$2,0),
IF(COUNTA(P19)>0,R$2,0),
IF(COUNTA(T19)>0,V$2,0),
IF(COUNTA(X19)>0,Z$2,0)

),

"INC")

So if there's an error, return "INC," otherwise
divide the SUM of these cells
by the SUM of

if cell H19 is full, give J2, otherwise 0
if cell L19 is full, give N2, otherwise 0
....

It's that last bit that cannot be the best way. I've tried SUMIF-type commands, but they won't tolerate a range defined by a series of comma-separated cells.

The point is a grade sheet, if this helps. In the A2:Z2 row, we find, separated here and there, some percentages for particular assignments. Now during the semester, not all grades have been filed, because not all assignments are complete. To have a running tally of a student's grade, I need to divide the total points awarded by the total percentage thus far accrued. That is, if there are 4 assignments worth 10%, 20%, 30%, and 40%, but at this point only assignments 1 and 2 are done, the running total must be divided only by SUM(10%,20%). So I want to look up whether a given grade cell is full (the grade has been filed), and if it is return the percentage for that assignment, and then add together all the returned percentages. (Oh -- the "INC" thing means no grades have been filed at all, or else there is something else weird or incomplete -- it's a flag.)

Can anyone see a way to do this without the kind of brute force I've done thus far? It's OK if there are only 5 assignments, but what if there are 20 or 30? The formulas become horrible and impossible to debug.

In addition, to be honest, I often use optional final exams, where the students are told "here is your grade if you don't take the exam, and here is your grade if you do." That's fine, but then they ask, "how well do I have to do to bump up my grade?" And to do that, I need to do WhatIf-type evaluations, and I need nice clean numbers to work with. So learning how to do this part now will make my life much easier later on.

Thanks!

[Andrew-R]

Could you post an example workbook, please?

While I can understand what you're trying to do it's very hard to work out from your description which cells contain grades and so forth.

[ChrisLehrich]

Attached. The formula I want to streamline is in column C, under "current grade."

[Andrew-R]

OK, in cell C4 use the formula =IFERROR(SUMPRODUCT(D4:Z4,--(D$3:Z$3="Pts"))/SUMPRODUCT(D$2:Z$2,--(D$3:Z$3="Pts"),--(D4:Z4<>"")),"INC")

At present that works up to column Z, but you can just expand the range to take in as many columns as you need for the number of tests you have.

[ChrisLehrich]

That's clever. I hadn't thought of using the infamous double unary to produce a 1/0 result as though it were an IF statement.

Thanks!

[Andrew-R]

Credit goes to the good people of this forum, who showed me how to do that. Glad it's working for you, please remember to edit your original post to mark this thread as solved. Thanks.