Prev: Compound Interest Rate or Growth Rate
Next: Starting drop down lists at the top
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:4295205C-F0D7-402F-8EC8-A1BB4D22F410(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 (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?


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