If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
prevent div/0 error
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 |
#2
|
|||
|
|||
prevent div/0 error
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 |
#3
|
|||
|
|||
prevent div/0 error
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? |
Thread Tools | |
Display Modes | |
|
|