MS Excel question

[nugundam93]

i've been a bit stumped with this MS Excel issue of mine and i can't recall how to work around this. here's the situation. i have a column that i want to average, and that column is based on each member cell's respective rows where those rows form parts of the equation in each column. for example, cell G6 of column G will have the equation:

=(C6*B6+D6+E6)/B6

and this goes on for a few cells of column G. and then they will be averaged like this, let's say, on cell G18:

=AVERAGE(G6,G7,G8,G9,G10,G11,G12,G13,G14,G15)

the thing is, if for example if G6 has no valid data on it, it'll give "#DIV/0!". this would then give the average on G18 as "#DIV/0!" as well. aside from manually inputting which column cells to consider (which makes the entire table well...tedious, is there a way to tell excel that for example, if G6 has no data, then to exclude it from the computation, like an "IF G6=0 THEN do not include in AVERAGE...yadda yadda"?

thanks in advance. :D

[the_wildthing]

Why not just put a value in B6 like 1 or something so it won't divide by 0?

Or, more complex, put an IF condition in G6 like IF(ISBLANK(B6),"",else whatever)

[nugundam93]

yun yung di ko alam eh, yung IF THEN na yan, yun nga sana balak ko. :D hehehehe...

pero kasi if i put 1 as value, edi skewed na yung data since dapat no value yun (example, absent yung agent who'll be putting those values in).

[rsnald]

tama yung suggestion ni da-wild... use the IF(ISBLANK(G6, "", ETC.)
Yung "" will put a NULL value on that cell and it will not be included in the average. try it and see.

[boybi]

instead of leaving the cell blank, put "0" (zero) in it.

[cj]

OT:

idol!!! lord master guru supremo rsnald! akala ko sa isuzu diesel engine at chemicals ka lang expert, pati pala sa excel!!

[buknoy2002]

pag nilagyan ng zero cell di ba sasama sa average yng zero?

[wildthing]

pag nilagyan ng zero cell di ba sasama sa average yng zero?

- yes...(kasi may laman yung cell) pero if you leave it blank hindi nya ika-count un cell na yun.

[wildthing]

etong equation na e2

=(C6*B6+D6+E6)/B6

ang dapat mong i-modify. Kasi pag nag resulta ito ng Div/0...mag-eerror na ang susunod mo na formula. To prevent this the denominator (B6 must not be = 0)

try this equivalent

IF(b6=0,0,(C6*B6+D6+E6)/B6)

So if the B6 cell becomes zero, then it would use the value 0 on that cell else it would follow your formula (C6*B6...etc). This would prevent the division by zero clause.

HTH