I am building a gradebook with limited knowledge of Excel. To get a weighted
percentage, I came up with this

=(I4/I$3)*0.05+(Y4/Y$3)*0.2+(AJ4/AJ$3)*0.2+(AU4/AU$3)*0.25+(AW4/AW$3)*0.05+(AY4/AY$3)*0.1+(BA4/BA$3)*0.15

However, the last few have zeros in the denominator (for example: BA3) as
there is no grade for them yet (exams, final projects, etc). How do I
exclude these from the formula to get a grade in the meantime until there is
a grade for the last few?

Thank You,
Austin

Take a look at the sample attached.



"Austin" wrote in message
...
I am building a gradebook with limited knowledge of Excel. To get a
weighted percentage, I came up with this

=(I4/I$3)*0.05+(Y4/Y$3)*0.2+(AJ4/AJ$3)*0.2+(AU4/AU$3)*0.25+(AW4/AW$3)*0.05+(AY4/AY$3)*0.1+(BA4/BA$3)*0.15

However, the last few have zeros in the denominator (for example: BA3) as
there is no grade for them yet (exams, final projects, etc). How do I
exclude these from the formula to get a grade in the meantime until there
is a grade for the last few?

Thank You,
Austin

hi, Austin !

I guess you need to preserve the (non-contiguous) range-columns for your data layout (?), so...
I build a scenario using a helper range as follows:

[A1:G1] with the column (letters) and row 1 as text-data (I1;Y1;AJ1;AU1;AW1;AY1;BA1)
[A2:G2] getting the column numbers of the above, formula: =column(indirect(a1)) <- copy-drag to the right
[A3:G3] getting the columns offset (starting @ 0), formula: =a2-$a2 <- copy-drag to the right
[A4:G4] the percentage to apply to each grade (i.e. 0.05;0.2;0.2;0.25;0.05;0.1;0.15)

now, in "some cell" (row 4) I used this array-formula (commited with ctrl+shift+enter)

=sum(if(isnumber(subtotal(9,offset($i$3,,$a$3:$g$3 ))/subtotal(9,offset(i4,,$a$3:$g$3))),
subtotal(9,offset($i$3,,$a$3:$g$3))/subtotal(9,offset(i4,,$a$3:$g$3)))*$a$4:$g$4)

if any doubts (or further information)... would you please comment ?
hth,
hector.

__ OP __
I am building a gradebook with limited knowledge of Excel. To get a weighted percentage, I came up with this
=(I4/I$3)*0.05+(Y4/Y$3)*0.2+(AJ4/AJ$3)*0.2+(AU4/AU$3)*0.25+(AW4/AW$3)*0.05+(AY4/AY$3)*0.1+(BA4/BA$3)*0.15
However, the last few have zeros in the denominator (for example: BA3) as there is no grade for them yet (exams, final projects, etc).
How do I exclude these from the formula to get a grade in the meantime until there is a grade for the last few?