Recognizing Real Values

[Guest]

How do I write a formula that allows for only using real updated values in
the following example:
In Column A I have values constantly being added from A1 down to A100, in
that order. Once a value has been set it doesn't change. Before a value is
added each cell has " #DIV/0! " in it. I want to find the average value of
all cells that have been filled in ... ignoring the ones that still have "
#DIV/0! " in it. What formula will only capture the updated values and ignore
the others?

 

[Guest]

Maybe you can try this:

In cell B1, =IF(ISERROR(A1),"-",A1)

HTH

 

[JE McGimpsey]

Why do you have #DIV/0! in your cells? It's generally bad practice to
have "expected" errors, since they may mask additional errors.

Perhaps you could change your formulae in A1:A100 from the form:

=A1/B1

to

=IF(B1=0,"",A1/B1)

since Average will ignore Text.