From: Austin on 14 Mar 2010 21:15 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
From: Dennis Tucker on 15 Mar 2010 00:14 Take a look at the sample attached. "Austin" <scienceguy2004(a)msn.com> wrote in message news:4295205CF0D7402F8EC8A1BB4D22F410(a)microsoft.com... > 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
From: H�ctor Miguel on 15 Mar 2010 01:37 hi, Austin ! I guess you need to preserve the (noncontiguous) rangecolumns 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 textdata (I1;Y1;AJ1;AU1;AW1;AY1;BA1) [A2:G2] getting the column numbers of the above, formula: =column(indirect(a1)) <> copydrag to the right [A3:G3] getting the columns offset (starting @ 0), formula: =a2$a2 <> copydrag 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 arrayformula (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?

Pages: 1 Prev: Compound Interest Rate or Growth Rate Next: Starting drop down lists at the top 