Formula Not Working
 

I have tried two different formulas to Average / Median a column of % here
are the two formulas,
=IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50))
or
=IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50))

Neither of them worked correctly. The ISERROR is in place to deal with cells
that are linked and do not show a % until other information is completed. If
the source cell goes unused the column maintains the ERROR, so the cell with
the formula has to see the error as 0 or blank to generate the average.

Formula Not Working
 

Are you saying that within the range M4:M50 there are errors like #N/A,
#VALUE!, #NUM!, #DIV/0! ?

If that's the case why don't you just fix those errors?

Try these array formulas that will account for errors:

=AVERAGE(IF(ISNUMBER(M4:M50),M4:M50))

=MEDIAN(IF(ISNUMBER(M4:M50),M4:M50))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"AccAdmin" wrote in message
...
I have tried two different formulas to Average / Median a column of % here
are the two formulas,
=IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50))
or
=IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50))

Neither of them worked correctly. The ISERROR is in place to deal with
cells
that are linked and do not show a % until other information is completed.
If
the source cell goes unused the column maintains the ERROR, so the cell
with
the formula has to see the error as 0 or blank to generate the average.

Formula Not Working
 

I used your Average formula and it worked perfect! Thank you very much for
your assistance.

Reason errors are left intact is due to formulas in each cell are linked to
cells awaiting information, once information is enter into source error
corrects itself.

"T. Valko" wrote:

Are you saying that within the range M4:M50 there are errors like #N/A,
#VALUE!, #NUM!, #DIV/0! ?

If that's the case why don't you just fix those errors?

Try these array formulas that will account for errors:

=AVERAGE(IF(ISNUMBER(M4:M50),M4:M50))

=MEDIAN(IF(ISNUMBER(M4:M50),M4:M50))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"AccAdmin" wrote in message
...
I have tried two different formulas to Average / Median a column of % here
are the two formulas,
=IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50))
or
=IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50))

Neither of them worked correctly. The ISERROR is in place to deal with
cells
that are linked and do not show a % until other information is completed.
If
the source cell goes unused the column maintains the ERROR, so the cell
with
the formula has to see the error as 0 or blank to generate the average.

Formula Not Working
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"AccAdmin" wrote in message
...
I used your Average formula and it worked perfect! Thank you very much for
your assistance.

Reason errors are left intact is due to formulas in each cell are linked
to
cells awaiting information, once information is enter into source error
corrects itself.

"T. Valko" wrote:

Are you saying that within the range M4:M50 there are errors like #N/A,
#VALUE!, #NUM!, #DIV/0! ?

If that's the case why don't you just fix those errors?

Try these array formulas that will account for errors:

=AVERAGE(IF(ISNUMBER(M4:M50),M4:M50))

=MEDIAN(IF(ISNUMBER(M4:M50),M4:M50))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"AccAdmin" wrote in message
...
I have tried two different formulas to Average / Median a column of %
here
are the two formulas,
=IF(ISERROR(AVERAGE(M4:M50)),0,(AVERAGE(M4:M50))
or
=IF(ISERROR(MEDIAN(M4:M50)),0,MEDIAN(M4:M50))

Neither of them worked correctly. The ISERROR is in place to deal with
cells
that are linked and do not show a % until other information is
completed.
If
the source cell goes unused the column maintains the ERROR, so the cell
with
the formula has to see the error as 0 or blank to generate the average.