Is it possible to hide errors in cells having array formulas

[manan]

Hi

I have array formulas in a worksheet. But some cells throw up error
value's such as div!/0 etc. i want to hide these errors but somehow am
not able to type the right function using iserror(cel_ref). Can anybody
tell me how to use ISERROR with array formulas

Regards

Manan

 

[Guest]

Hi Manan!

You can use the ISERROR together with conditional IF function. To do that
you may do enter the ff formula..

=IF(ISERROR(formula)=TRUE,"",formula)

if this is an array formula you should edit the existing one using the
CTRL+SHIFT+ENTER combination. After editing the formula then enter it the
using CTRL+SHIFT+ENTER also.

Hope this helps!

 

[manan]

Hi

I tried doing it but its making all the cells blanks Even the one
which are not having any error

=IF(ISERROR(AVERAGE(IF($D$2:$D$345=$A349,E$2:E$345))),
",AVERAGE(IF($D$2:$D$345=$A349,E$2:E$345)))

I used the the above mentioned formula and used ctrl+shift+enter afte
this.

Maybe you can let me know if i am typing the formula wrong.

Thanks for your help.

Regards

Manan

Hi Manan!

You can use the ISERROR together with conditional IF function. To d
that
you may do enter the ff formula..

=IF(ISERROR(formula)=TRUE,"",formula)

if this is an array formula you should edit the existing one using the
CTRL+SHIFT+ENTER combination. After editing the formula then enter i
the
using CTRL+SHIFT+ENTER also.

Hope this helps!

 

[aidan.heritage]

IF the errors are being hidden for purely cosmetic reasons, then you
might find it easier to use conditional formatting with the IsError
function - if there is an error, set the font colour to the cell
background colour (white in most cases) - error is still there, but
unless you select the cell as a block you cannot see it!!

 

[Guest]

Hi again Manan!

Does the ff formula AVERAGE(IF($D$2:$D$345=$A349,E$2:E$345)) result in
error? Or intermediate values within ranges $D$2:$D$345 and E$2:E$345 contain
error values? You may try incorporating the IF-ISERROR combination there
instead for the whole array. When performing AVERAGE() on a range containing
error values result into error also.

Hope this helps!

 

[manan]

Hi

I am sorry to bother you again. The issue is taht in colum D i have
various countries. In column e-J i have data pertaining to diofferent
years for companies. What i want to do using array formula is that
find average for all the companies in a particular country. Now it may
happen that for aparticular year some companies may not have data. Let
say that i am trying to find a avarage for Canada. It has around 10
companies. Now all these companies don have any data in 2005. Which
means taht when i ask for a average it will give me an error div/0!
because it has no numbers to get an average.

I need the average for all countries for each year. So if a particular
year for aparticular country gives me an div/0! error i want to hide
that error and keep it blank. Because then i have to take grandaverage
for all teh years together for taht country. In this case if a
particular yeaar has div/0! error then the resulting grand average is
also an error which i don want. Hence the need to hide the error so
taht the grand avearge computation ignores teh cell and computes
average pertaining to years for which data is available.

Hope i have made myself clear to you.

Regards

Manan

 

[Pete_UK]

Try this, then:

=IF(iserror(E$2:E$345),0,AVERAGE(IF($D$2:$D$345=$A$349,E$2:E$345)))

Commit with CRTL-SHIFT-ENTER, then copy across to column J.

Hope this helps.

Pete

 

[manan]

Hi Pete

Thanks for your reply. But did not solve the problem. Because i don
want to take zero as the value in case if there is not data in column
e. This will count the cell as a value and the average given out be
wrong.

Moreover even if i keep it as zero. this formula is making all the
cells to which i copy the formula as 0.

Hope i made myself clear

Many regards

Manan

 

[Pete_UK]

Ok, here's a variation which should give you what you want:

=SUM(IF(ISERROR(E$2:E$345),0,IF($D$2:$D$345=$A$349,E$2:E$345,0)))
/SUM(IF(ISERROR(E$2:E$345),0,IF($D$2:$D$345=$A$349,1,0)))

All one formula - again, CSE to commit, as this is an array formula.

Instead of using AVERAGE, this uses SUM divided by the count, over the
ranges if they meet the criteria, with errors ignored.

Hope this helps.

Pete

 

[Dave Peterson]

Maybe you could just check to see what the divisor would be. If it's 0, then
return "".

=IF(COUNTIF($D$2:$D$345,$A$349)=0,"",AVERAGE(IF($D$2:$D$345=$A349,E$2:E$345)))
(still an array formula)

Or this non-array formula:

=IF(COUNTIF($D$2:$D$345,$A$349)=0,"",
SUMIF($D$2:$D$345,$A$349,$E$2:$E$345)/COUNTIF($D$2:$D$345,$A$349))

(Watch out for the addresses. I changed them when I tested and changed (er,
tried to change them back) in the post--not excel itself. I think I got all of
them.)

 

[manan]

Hi

Thanks you both for ur reply.
But both the formulas are checking for divisor on how many times the
country is occuring. Colomn D which has the name of countries is there
for all the companies. Issue is that data is not present in column e to
j (heading being different years). So lets say that i am trying to find
a average for canada then by your formula its checking how many times
is canada there in column d and using it as a divisor which will never
be zero as country data is present. so lets say there are 20 companies
from canada out of 340 odd companies. Now for these 20 companies it is a
possibility that for year 2005 there is data ( for which i need teh
average) only for 15 companies. So i want that while computing the
average the divisor should be 15 and not 20.
and lets say that if for year 2005 for canada there is not even one
single data then it should give me blank as the average instead of
div/0!

Hope my query is clear enough. Looking forward to get over this
headache.

Thanks in adavnce

Manan

 

[Dave Peterson]

I think...

=if(sumproduct(--(d2:d345=a349),(e2:e345))=0,"",
sumproduct(--(d2:d345=a349),(e2:e345))
/sumproduct(--(d2:d345=a349),--(isnumber(e2:e345))))

if you only have 0's (blanks evaluate to 0') in E2:E345 for that company in
a349, then show "".

Otherwise, sum all the values in E2:E345 and divide by only the count of numbers
(non-blanks) in E2:e345 that have data in D2345 that match a349.

The middle sumproduct could still be replaced with =sumif().

=IF(SUMPRODUCT(--(D2345=A349),(E2:E345))=0,"",
SUMIF(D2345,A349,E2:E345)
/SUMPRODUCT(--(D2345=A349),--(ISNUMBER(E2:E345))))

I think that this matches what you want--but I didn't test it.

 

[Pete_UK]

I misunderstood - I thought you had errors in column E which you wanted
to ignore, but you now seem to be saying that you could have blanks in
column E and it is these that need to be ignored. If this is the case
then the following should work where you have some data:

=SUM(IF(E$2:E$345="",0,IF($D$2:$D$345=$A$349,E$2:E$345,0)))
/SUM(IF(E$2:E$345="",0,IF($D$2:$D$345=$A$349,1,0)))

The first SUM will ignore any blank entries in E and add up the total
from column E where column D matches A349. The second SUM acts as a
count of those same entries, thus giving you the average.

However, if you have no entries in the 2005 column for Canada, then
this will return #DIV/0. The usual way to avoid this is along the
lines:

=IF(ISERROR(formula),"",formula),

but that would obviously make the formula very long. A slightly shorter
version would test for the denominator being zero, as Dave suggested.

Hope this helps - we'll get there eventually.

Pete

 

[manan]

Hi Pete

Sorry to bother you again and again
But now what's happening is that the one's which have data in e2:e345
and for which average should be computed is giving error #value.

And just to remind you that range d2:d345 does not have numeric data.
Its Name of countries. So howcan we havea sumproduct of two arrays in
which one is a string and the other is numeric (e2:e345). Sorry i am
new to excel so maybe i am interpreting the formula wrong. Please
correct me if i am wrong.

Looking forward to help on the issue

Thanks in advance

Regards
Manan