Simple? How do I average data while skipping #N/A errors?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

duncanm - 16 Sep 2005 10:58 GMT

Hi,
I thought I would find a simple soution to this problem, but I haven'
so far. I am simply wanting to average a large number of data groups
but I really need to be able to skip the errors automatically. Is thi
possible?

The reason I need this is because I will have averages running alon
the bottom of columns and along the end of the rows (so there are a lo
of calculations), and the #N/As need to stay in because they translat
through as discontinuities in line graphs I have plotted from th
data.

Can you help? Many thanks

duncan

Reply to this Message

Dave Peterson - 16 Sep 2005 11:40 GMT

One way:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Make the range big enough to match your data, but don't use the whole column.

> Hi,
> I thought I would find a simple soution to this problem, but I haven't
[quoted text clipped - 17 lines]
> duncanm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27240
> View this thread: http://www.excelforum.com/showthread.php?threadid=468149

Signature

Dave Peterson

Reply to this Message