Calculate average of a range, but exclude errors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Piotr - 08 May 2008 08:18 GMT

Hello!

In my workbook I have several worksheets. In cell B2 of every worksheet,
there is an average from the data in that worksheet. As this is a
template, some data is not entered yet, and some B2 cells show error
(div by 0). Now, I wanted to calculate the average from B2 cells of all
worksheets, but exclude all cells with errors.

My formula

=Average(Sheet1:Sheet12!B2)

How do I modify this to work?

Thakks
Peter

Reply to this Message

Niek Otten - 08 May 2008 11:29 GMT

Hi Peter,

Change the formulas of the cells that you want to average to

=IF(ISERROR(YourFormula),"",YourFormula)

or, even better, check for the divider being zero and generate "" if that is the case.

The AVERAGE() function ignores the "" cells.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello!
|
[quoted text clipped - 12 lines]
| Thakks
| Peter

Reply to this Message

Gary''s Student - 08 May 2008 11:41 GMT

See Rosenfeld's comments in :

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse
_thread/thread/939b06ebd6a6b561/1f1ff77e04a97858?hl=en&lnk=st&q=average+exclude+
errors+%3A*Excel

and adapt to your needs.

Signature

Gary''s Student - gsnu2007h

> Hello!
>
[quoted text clipped - 12 lines]
> Thakks
> Peter

Reply to this Message