Help Appreciated

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:
 
S

sheywood

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.
 
L

Leo Heuser

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.
 
L

Leo Heuser

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.
 
A

ALANBATESON

Leo,

Thank you very much, your fix was correct and I am now sorted,

Regards,

Alan:D
 
J

Jon

-----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:



------------------------------------------------
[/url]
~~ 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
 
R

Ron de Bruin

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.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Jon said:
-----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:



------------------------------------------------
[/url]
~~ 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
Click to expand...
 
Top