Formula Not Working

  • Thread starter Thread starter AccAdmin
  • Start date Start date
A

AccAdmin

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.
 
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)
 
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.
 
Back
Top