Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
I have the following equation:
=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
sorry the mestake I forgot the IF
=if(iserror(formula,0,formula)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Barb Reinhardt" escreveu: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
Hi Barb,
Why not to remove the N/A values on the source? use =iserror(formula,0,formula) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Barb Reinhardt" escreveu: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
What do you want to happen if there are N/A values? Return 0? One possible
solution would be to conditionally format the range B19:AD19 to show 0 where there are N/A values. Or show 1 if you don't want the rest of your calculations to be affected. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
I want NA to be displayed in B19:AD19 if there isn't a result. I need the
sumproduct formula to be able to pull them out. I thought I could use something like ISNUMBER() around the range that had the NA in them, but can't seem to get that to work. "Dave F" wrote: What do you want to happen if there are N/A values? Return 0? One possible solution would be to conditionally format the range B19:AD19 to show 0 where there are N/A values. Or show 1 if you don't want the rest of your calculations to be affected. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
Well, if you're looking to highlight NAs in that array, then maybe you could
conditionally format NAs to appear as 1s with a red background. That way you can use the array in your sumproduct calculation AND show, graphically, where the NAs are. It seems to me that, as you have your formula now, SUMPRODUCT is applied to that entire range. I don't know how you would tell SUMPRODUCT to look at that whole range, and only use the values that are not NA. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I want NA to be displayed in B19:AD19 if there isn't a result. I need the sumproduct formula to be able to pull them out. I thought I could use something like ISNUMBER() around the range that had the NA in them, but can't seem to get that to work. "Dave F" wrote: What do you want to happen if there are N/A values? Return 0? One possible solution would be to conditionally format the range B19:AD19 to show 0 where there are N/A values. Or show 1 if you don't want the rest of your calculations to be affected. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
I've tried this and it doesn't work either:
=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),--ISNUMBER('Director Data Summary'!$B$19:$AD$19),('Director Data Summary'!$B$19:$AD$19))) "Dave F" wrote: Well, if you're looking to highlight NAs in that array, then maybe you could conditionally format NAs to appear as 1s with a red background. That way you can use the array in your sumproduct calculation AND show, graphically, where the NAs are. It seems to me that, as you have your formula now, SUMPRODUCT is applied to that entire range. I don't know how you would tell SUMPRODUCT to look at that whole range, and only use the values that are not NA. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I want NA to be displayed in B19:AD19 if there isn't a result. I need the sumproduct formula to be able to pull them out. I thought I could use something like ISNUMBER() around the range that had the NA in them, but can't seem to get that to work. "Dave F" wrote: What do you want to happen if there are N/A values? Return 0? One possible solution would be to conditionally format the range B19:AD19 to show 0 where there are N/A values. Or show 1 if you don't want the rest of your calculations to be affected. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
Hi Barb
Try the Array entered formula {=IF(B24=0,NA(), SUM(($B$1:$AD$1=$A37)* ('Director Data Summary'!$B$2:$AD$2=B$22)* IF(ISNUMBER('Director Data Summary'!$B$19:$AD$19, 'Director Data Summary'!$B$19:$AD$19,0)))} Use Ctrl+Shift+Enter (CSE) to commit or edit the formula instead of Enter. Do not type the curly braces { } yourself, Excel will enter them when you use CSE -- Regards Roger Govier "Barb Reinhardt" wrote in message ... I've tried this and it doesn't work either: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),--ISNUMBER('Director Data Summary'!$B$19:$AD$19),('Director Data Summary'!$B$19:$AD$19))) "Dave F" wrote: Well, if you're looking to highlight NAs in that array, then maybe you could conditionally format NAs to appear as 1s with a red background. That way you can use the array in your sumproduct calculation AND show, graphically, where the NAs are. It seems to me that, as you have your formula now, SUMPRODUCT is applied to that entire range. I don't know how you would tell SUMPRODUCT to look at that whole range, and only use the values that are not NA. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I want NA to be displayed in B19:AD19 if there isn't a result. I need the sumproduct formula to be able to pull them out. I thought I could use something like ISNUMBER() around the range that had the NA in them, but can't seem to get that to work. "Dave F" wrote: What do you want to happen if there are N/A values? Return 0? One possible solution would be to conditionally format the range B19:AD19 to show 0 where there are N/A values. Or show 1 if you don't want the rest of your calculations to be affected. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
I actually ended up using this array entered formula last week
=IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--($B$2:$AD$2=C$22),(IF(ISNA($B$19:$AD$19)=FALSE,$B$ 19:$AD$19)))) I still don't understand why I couldn't use this --(ISNUMBER($B$19:$AD$19)) as part of my array formula. It just never worked. Any ideas? Thanks "Roger Govier" wrote: Hi Barb Try the Array entered formula {=IF(B24=0,NA(), SUM(($B$1:$AD$1=$A37)* ('Director Data Summary'!$B$2:$AD$2=B$22)* IF(ISNUMBER('Director Data Summary'!$B$19:$AD$19, 'Director Data Summary'!$B$19:$AD$19,0)))} Use Ctrl+Shift+Enter (CSE) to commit or edit the formula instead of Enter. Do not type the curly braces { } yourself, Excel will enter them when you use CSE -- Regards Roger Govier "Barb Reinhardt" wrote in message ... I've tried this and it doesn't work either: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),--ISNUMBER('Director Data Summary'!$B$19:$AD$19),('Director Data Summary'!$B$19:$AD$19))) "Dave F" wrote: Well, if you're looking to highlight NAs in that array, then maybe you could conditionally format NAs to appear as 1s with a red background. That way you can use the array in your sumproduct calculation AND show, graphically, where the NAs are. It seems to me that, as you have your formula now, SUMPRODUCT is applied to that entire range. I don't know how you would tell SUMPRODUCT to look at that whole range, and only use the values that are not NA. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I want NA to be displayed in B19:AD19 if there isn't a result. I need the sumproduct formula to be able to pull them out. I thought I could use something like ISNUMBER() around the range that had the NA in them, but can't seem to get that to work. "Dave F" wrote: What do you want to happen if there are N/A values? Return 0? One possible solution would be to conditionally format the range B19:AD19 to show 0 where there are N/A values. Or show 1 if you don't want the rest of your calculations to be affected. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT question
Hi Barb
My solution used IF(ISNUMBER()) which produces the same result as IF(NOT(ISNA())) or IF(ISNA()=FALSE) It is the use of the IF() clause that is important. That is saying only use the value from B19:AD19 IF the value is a number or is not #N/A. Without the IF clause, as you were trying, --ISNUMBER() operating on a range containing numbers and #N/A's value was returning an array of 1's and 0's, but then multiplying the result by the same range give a #N/A result as 0 * #N/A will be #N/A thereby invalidating the whole result. The IF clause is causing only the values that are not #N/A to be used in the remainder of the calculation. -- Regards Roger Govier "Barb Reinhardt" wrote in message ... I actually ended up using this array entered formula last week =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--($B$2:$AD$2=C$22),(IF(ISNA($B$19:$AD$19)=FALSE,$B$ 19:$AD$19)))) I still don't understand why I couldn't use this --(ISNUMBER($B$19:$AD$19)) as part of my array formula. It just never worked. Any ideas? Thanks "Roger Govier" wrote: Hi Barb Try the Array entered formula {=IF(B24=0,NA(), SUM(($B$1:$AD$1=$A37)* ('Director Data Summary'!$B$2:$AD$2=B$22)* IF(ISNUMBER('Director Data Summary'!$B$19:$AD$19, 'Director Data Summary'!$B$19:$AD$19,0)))} Use Ctrl+Shift+Enter (CSE) to commit or edit the formula instead of Enter. Do not type the curly braces { } yourself, Excel will enter them when you use CSE -- Regards Roger Govier "Barb Reinhardt" wrote in message ... I've tried this and it doesn't work either: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),--ISNUMBER('Director Data Summary'!$B$19:$AD$19),('Director Data Summary'!$B$19:$AD$19))) "Dave F" wrote: Well, if you're looking to highlight NAs in that array, then maybe you could conditionally format NAs to appear as 1s with a red background. That way you can use the array in your sumproduct calculation AND show, graphically, where the NAs are. It seems to me that, as you have your formula now, SUMPRODUCT is applied to that entire range. I don't know how you would tell SUMPRODUCT to look at that whole range, and only use the values that are not NA. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I want NA to be displayed in B19:AD19 if there isn't a result. I need the sumproduct formula to be able to pull them out. I thought I could use something like ISNUMBER() around the range that had the NA in them, but can't seem to get that to work. "Dave F" wrote: What do you want to happen if there are N/A values? Return 0? One possible solution would be to conditionally format the range B19:AD19 to show 0 where there are N/A values. Or show 1 if you don't want the rest of your calculations to be affected. Dave -- Brevity is the soul of wit. "Barb Reinhardt" wrote: I have the following equation: =IF(B24=0,NA(),SUMPRODUCT(--($B$1:$AD$1=$A37),--('Director Data Summary'!$B$2:$AD$2=B$22),('Director Data Summary'!$B$19:$AD$19))) It works if the last range doesn't have any N/A values in it. What do I need to change to get it to ignore the N/A values? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
sumproduct question | Excel Worksheet Functions |