Compound array question - what am I doing wrong?

[Daesthai]

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?

 

[Daesthai]

D'oh! Nevermind. I found the misplaced parenthesis.

 

[Ron Rosenfeld]

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

 

[Daesthai]

Thank you. that simplified things quite a bit.

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

 

[Ron Rosenfeld]

Thank you. that simplified things quite a bit.

You're welcome. Glad I could be of help.

--ron