avg, min and max

  • Thread starter Thread starter Annika
  • Start date Start date
A

Annika

Excel is silly:
If cell range A1:A10 is empty,

=average(a1:a10) will result in a #DIV/0! error message,

=max(a1:a10) will result in 0,

and

=min(a1:a10) will also result in 0.

I work with large intrinsic data sets, but sometimes have
to use these 3 functions on them in macros using VBA. I
need to avoid the #DIV/0! errors that occur in the
averaging column, and do this by using the formula
=IF(ISERROR(AVERAGE(a1:a10)), "", AVERAGE(a1:a10)). By
this I replace the error messages with blanks.

So here's my question:
When I'm using the MIN or MAX functions, is there a code
(excel worksheet formula or VBA) that can make the cell
show blank instead of a "0" if the range that I am using
is empty?
Please help!
 
This might sound trivial, but can't you just use the same
logic you used for getting rid of the #div/0 error message
you used when calculating the average. By this I mean,

for the max,

=IF(ISERROR(AVERAGE(K16:K28)), "", MAX(K16:K28))

and for the min,

=IF(ISERROR(AVERAGE(K16:K28)), "", MIN(K16:K28))

Same logic as before.

Jim
 
Back
Top