Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to supress #N/A in an array formula.
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to supress #N/A in an array formula.
Maybe...
=IF(ISERROR(SUM(((Sheet1!$O$3:$O$6000=A3)*(Sheet1! $I$3:$I$6000<"")*(Sheet1!$N$3:$N$6000="NO")))),"N O CALLS",(SUM(((Sheet1!$O$3:$O$6000=A3)*(Sheet1!$I$3 :$I$6000<"")*(Sheet1!$N$3:$N$6000="NO"))))) "Phil" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to supress #N/A in an array formula.
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? -- Biff Microsoft Excel MVP "Phil" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to supress #N/A in an array formula.
On 6 May, 16:57, "T. Valko" wrote:
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 "Phil" wrote in message ... How to supress #N/A in anarrayformula. Using Excel 2003. Theformulabelow 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 thisformula 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.- Hide quoted text - - 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to supress #N/A in an array formula.
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 "Phil" wrote in message ... On 6 May, 16:57, "T. Valko" wrote: 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 "Phil" wrote in message ... How to supress #N/A in anarrayformula. Using Excel 2003. Theformulabelow 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 thisformula 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.- Hide quoted text - - 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to supress #N/A in an array formula.
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 "Phil" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Supress #Ref! error | Excel Worksheet Functions | |||
Supress (blank) in pivottable | Excel Discussion (Misc queries) | |||
Supress Scientific Numbers?? | New Users to Excel | |||
#div/0! how do i supress? | Excel Worksheet Functions | |||
Supress error message | Excel Discussion (Misc queries) |