As Biff says, you should get rid of the errors, but if that is not feasible,
you could try

=AVERAGE(IF(NOT(ISERROR(J7:J21)),IF(J7:J21<>"",J7:J21)))

which is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"lighting" <lighting.20257y_1134610200.6609@excelforum-nospam.com> wrote in
message news:lighting.20257y_1134610200.6609@excelforum-nospam.com...
>
> Norman -
>
> Thank you for the reply. Yes I realize this. But for instance in a
> large worksheet/workbook where some cells that are to be averaged may
> not be populated yet (due to awaiting a calculation from another field)
> then until there is at least 1 value ready the cell we are trying to
> create for this example displays "#VALUE!". We would like to not have
> numerous cells display this while they await other data so we had hoped
> to use a calculation that would allow "" to be displayed if all the
> cells to be averaged were aslo displaying ""
>
> Any further thoughts?
>
>
> --
> lighting
> ------------------------------------------------------------------------
> lighting's Profile:
http://www.excelforum.com/member.php...o&userid=29656
> View this thread: http://www.excelforum.com/showthread...hreadid=493667
>