How to supress #N/A in an array formula.

[Phil]

How to supress #N/A in an array formula.

Using Excel 2003.

The formula below calculates where a call has been replied to within
it's alllocated target time.

=SUM((('Sheet1'!$O$3:$O$6000=A3)*('Sheet'!$I$3:$I$6000<>"")*('Sheet1'!
$N$3:$N$6000="NO")))
A B C

A. Cell A3 contains a label to a period of time, say a specific week,
lets say week 1.

B. Ensures thats the cell containing the completion indicator is not
blank, i.e. the action (reply) has been performed.

C. Determines if the target was failed.


So, my criteria are in the specified time period if the completion
indicator is not blank (this would contain a date that the action was
taken) and the action did not fail it's target (indicated by a "NO")
then it would be included in the total returned and so forth.

My problem here is that if during a given time period no calls were
booked, no data matches week 1 in the target sheet, then this formula
automatically returns #N/A, how do I prevent this.
If I were doing a simple LOOKUP I'd use
=IF(ISNA(VLOOKUP(..x..y..z..)),"NO CALLS",VLOOKUP(..x..y..z..))) etc

Any ideas anyone?

Many thanks, in anticipation.

Phil.

 

[Gaurav]

Maybe...

=IF(ISERROR(SUM(((Sheet1!$O$3:$O$6000=A3)*(Sheet1!$I$3:$I$6000<>"")*(Sheet1!$N$3:$N$6000="NO")))),"NO
CALLS",(SUM(((Sheet1!$O$3:$O$6000=A3)*(Sheet1!$I$3:$I$6000<>"")*(Sheet1!$N$3:$N$6000="NO")))))

 

[T. Valko]

If none of your conditions are met your formula should return 0. When none
of the conditions are met are there any #N/A errors in *any* of the
referenced ranges?

 

[Phil]

If none of your conditions are met yourformulashould return 0. When none
of the conditions are met are there any #N/A errors in *any* of the
referenced ranges?

--
Biff
Microsoft Excel MVP

















- Show quoted text -

Okay, I guess that's correct, when the first criteria isn't met then
none of them will be, therefore I am really asking how to supress #N/A
when no criteria are met

 

[Peo Sjoblom]

Suppress the #N/A in the formula that returns it. Meaning the sum formula
does not returns
#N/A by itself, it must be in one or more of the ranges you are using in the
sum formula


--


Regards,


Peo Sjoblom

If none of your conditions are met yourformulashould return 0. When none
of the conditions are met are there any #N/A errors in *any* of the
referenced ranges?

--
Biff
Microsoft Excel MVP

















- Show quoted text -

Okay, I guess that's correct, when the first criteria isn't met then
none of them will be, therefore I am really asking how to supress #N/A
when no criteria are met

 

[Shane Devenshire]

Hi Phil,

Highlight the cells where the problem might occur and choose Format,
Conditional Formatting, from the first drop down choose Formula is, in the
next box type =ISNA(A1) click the Format button and set the font color to
White.

In this example A1 is the first cell with you array formulas and it is the
active cell of the selection.

Cheeres,
Shane Devenshire
Microsoft Excel MVP