Help Appreciated

  • Thread starter Thread starter ALANBATESON
  • Start date Start date
A

ALANBATESON

I am trying to avaerage a column that contains percentages and also
errors, #DIV=0!, but the cells that the errors are in will change as
more data is entered so I must keep the formulae as it is all be it
returning an error at present, the cell range is B5:B39, ANY HELP
APPRECIATED,

ALAN:cool:
 
try =subtotal(1,range) where range is the range of cells you want to
average. subtotal is good at ignoring non-numeric cells.
When you add new items use insert and the range will adjust
automatically.
 
Alan

Here's one way

=AVERAGE(IF(NOT(ISERROR(B5:B39)),B5:B39))

The formula is an array formula and must be entered with
<Shift><Ctrl><Enter>
also if edited later. If done correctly, Excel will display the formula in
the
formula bar enclosed in curly brackets { } Don't enter these brackets
yourself.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Just to clarify:
AVERAGE() also ignores non-numeric cells, but
neither AVERAGE() or SUBTOTAL() ignores
*errors* in a cell.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
-----Original Message-----
I am trying to avaerage a column that contains percentages and also
errors, #DIV=0!, but the cells that the errors are in will change as
more data is entered so I must keep the formulae as it is all be it
returning an error at present, the cell range is B5:B39, ANY HELP
APPRECIATED,

ALAN:cool:



------------------------------------------------

~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
Click to expand...


Alan

What you need to do is put in an if statement so that the
percentages aren't calculated if there is no data for
them. The initial error is there because it is dividing
by a zero at some point and I presume this is where the
data is coming in. If you put something like this:

=IF(D1=0,"",CALCULATION)

Here D1 is where the data is coming from so if there is no
data then the cell remains blank. Otherwise it does the
calculation (obviously replace "CALCULATION" with whatever
you had in the cell in the first place).

You might also need to do some form of counting statement
for the average calc so that you are only averaging the
ones that you have data for (ie dividing by the number of
cells that are greater than 0).

Jon
 
Try this Jon

=AVERAGE(IF(NOT(ISERROR(B1:B50)),B1:B50))

The formula is an array formula and must be entered with Ctrl-Shift-Enter.
 
Back
Top