Compound array question - what am I doing wrong?
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? |
D'oh! Nevermind. I found the misplaced parenthesis.
|
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 |
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 |
On Wed, 12 Oct 2005 21:06:04 -0700, "Daesthai"
wrote: Thank you. that simplified things quite a bit. You're welcome. Glad I could be of help. --ron |
All times are GMT +1. The time now is 08:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com