Thank you. that simplified things quite a bit.
"Ron Rosenfeld" wrote:
On Wed, 12 Oct 2005 19:43:02 -0700, "Daesthai"
wrote:
I'm trying to use an array to average a row of numbers, ignore zeroes, and
display a "-" if there is a div/0 error.
Using the format =IF(ISERROR(old_formula),"",old_formula) listed in help to
hide the error message, I end up with:
=IF(ISERROR(AVERAGE(IF(b4:h4<0,b4:h4,False)))) ,"-",AVERAGE(IF(b4:h4<0,b4:h4,False))
But I get an error textbox saying it is an incorrectly written array. What
do I need to change to get the desired results?
Well, I think that the only time you should get a DIV/0 error is if there are
only 0's or blanks in the range to be averaged (rng). So perhaps
=IF(SUM(rng)=0,"-",AVERAGE(IF(rng<0,rng)))
(as an array formula)
--ron
|