Average cells with #DIV/0!

K

Keller2

I have a WORKBOOK with series of monthly worksheets with a TOTALS worksheet
that pulls the values from the appropriate monthly worksheets which are input
as each months numbers are added. The TOTALS worksheet needs to average only
the monthly totals to date. The cells in the TOTALS worksheet display #DIV/0!
until the corresponding worksheet is updated each month.

How to I get an average of just the cells that contain values and not count
the cells that display #DIV/0!, and do not have values yet?
 
P

Pete_UK

Try this array* formula:

=AVERAGE(IF(NOT(ISERROR(A1:A12)),A1:A12))

* An array formula has to be committed using CTRL-SHIFT-ENTER (CSE)
rather than the usual ENTER. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. Use CSE again whenever you edit the
formula.

Obviously, adjust the ranges to suit your situation.

Hope this helps.

Pete
 
G

Gary''s Student

Say we have data in A1 thru A10, but some may have errors

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

an array formula placed with CNTRL-SHFT-ENTER rather than just the ENTER key
will exclude errors.
 
K

Keller2

It worked!!!! Thank you so very much.

Pete_UK said:
Try this array* formula:

=AVERAGE(IF(NOT(ISERROR(A1:A12)),A1:A12))

* An array formula has to be committed using CTRL-SHIFT-ENTER (CSE)
rather than the usual ENTER. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself. Use CSE again whenever you edit the
formula.

Obviously, adjust the ranges to suit your situation.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top