MS Excel question

**nugundam93** · January 4th, 2006 02:33 AM

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** · January 4th, 2006 02:49 AM

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** · January 4th, 2006 03:17 AM

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** · January 4th, 2006 09:49 AM

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** · January 4th, 2006 09:51 AM

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

cj · January 4th, 2006 10:14 AM

OT:

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

**buknoy2002** · January 4th, 2006 08:42 PM

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

**wildthing** · January 4th, 2006 08:47 PM

Originally Posted by buknoy2002

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** · January 4th, 2006 09:05 PM

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