Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
I have the following array formula:
{=SUM(IF(MONTH('2008'!B$2:B$746)=$I4,1,0))} In col I I have values 1 to 12. I would expect this to give me a summary of monthly occurences based on column B which contains dates. The problem is in the first month. It seems that if the cell is blank, the ligical retuirns 1, so with nothing in column B the formula returns a value of 45. I have tried ISBLANK with no success, since the values in column B are continually being added to. All other months work except 01. Any suggestions? David M. Kellerman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
=SUM(IF((MONTH('2008'!B$2:B$746)=$I4)*('2008'!B$2: B$746<""),1))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidK" wrote in message ... I have the following array formula: {=SUM(IF(MONTH('2008'!B$2:B$746)=$I4,1,0))} In col I I have values 1 to 12. I would expect this to give me a summary of monthly occurences based on column B which contains dates. The problem is in the first month. It seems that if the cell is blank, the ligical retuirns 1, so with nothing in column B the formula returns a value of 45. I have tried ISBLANK with no success, since the values in column B are continually being added to. All other months work except 01. Any suggestions? David M. Kellerman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
Try this non-array version:
=SUMPRODUCT(--(ISNUMBER('2008'!B$2:B$746)),--(MONTH('2008'!B$2:B$746)=$I4)) -- Biff Microsoft Excel MVP "DavidK" wrote in message ... I have the following array formula: {=SUM(IF(MONTH('2008'!B$2:B$746)=$I4,1,0))} In col I I have values 1 to 12. I would expect this to give me a summary of monthly occurences based on column B which contains dates. The problem is in the first month. It seems that if the cell is blank, the ligical retuirns 1, so with nothing in column B the formula returns a value of 45. I have tried ISBLANK with no success, since the values in column B are continually being added to. All other months work except 01. Any suggestions? David M. Kellerman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
Biff,
Thanks and Happy New Year! This does the job ... now the qwuestion: what are the -- in the formula? never saw those before. On Dec 31 2007, 1:35*pm, "T. Valko" wrote: Try this non-array version: =SUMPRODUCT(--(ISNUMBER('2008'!B$2:B$746)),--(MONTH('2008'!B$2:B$746)=$I4)) -- Biff Microsoft Excel MVP "DavidK" wrote in message ... I have the following array formula: {=SUM(IF(MONTH('2008'!B$2:B$746)=$I4,1,0))} In col I I have values 1 to 12. *I would expect this to give me a summary of monthly occurences based on column B which contains dates. The problem is in the first month. *It seems that if the cell is blank, the ligical retuirns 1, so with nothing in column B the formula returns a value of 45. I have tried ISBLANK with no success, since the values in column B are continually being added to. All other months work except 01. *Any suggestions? David M. Kellerman- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidK" wrote in message ... Biff, Thanks and Happy New Year! This does the job ... now the qwuestion: what are the -- in the formula? never saw those before. On Dec 31 2007, 1:35 pm, "T. Valko" wrote: Try this non-array version: =SUMPRODUCT(--(ISNUMBER('2008'!B$2:B$746)),--(MONTH('2008'!B$2:B$746)=$I4)) -- Biff Microsoft Excel MVP "DavidK" wrote in message ... I have the following array formula: {=SUM(IF(MONTH('2008'!B$2:B$746)=$I4,1,0))} In col I I have values 1 to 12. I would expect this to give me a summary of monthly occurences based on column B which contains dates. The problem is in the first month. It seems that if the cell is blank, the ligical retuirns 1, so with nothing in column B the formula returns a value of 45. I have tried ISBLANK with no success, since the values in column B are continually being added to. All other months work except 01. Any suggestions? David M. Kellerman- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
Bob,
I read the article, which identifies the use of the double unary. I even understand what the double unary is. What I do not understand is the function of the double unary in the formula: =SUMPRODUCT(--(ISNUMBER('2008'!B$2:B$746)),--(MONTH('2008'!B$2:B$746)= $I4)) Maybe my understanding is flawed. I see this as creating an array of 0's for every blank cell in B2:B746 and 1's where there is a numeric value(date being stored as a serialized number). Therefore, (--(ISNUMBER('2008'!B$2:B$746)) should work as well as ((ISNUMBER('2008'!B$2:B$746)). And it does. What confuses me is the second array. --(MONTH('2008'!B$2:B$746)=$I4) should do the same, creating a 1 or 0 to be multiplied by the corresponding value from the ISNUMBER array. I do not see why (MONTH('2008'!B$2:B$746)=$I4)) does not produce the same result without the operator. Is it because it is a logical value versus numeric? Regardless, thank you for the introduction to SUMPRODUCT. David |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
I don't understand what you mean by the statement ... Therefore,
(--(ISNUMBER('2008'!B$2:B$746)) should work as well as ((ISNUMBER('2008'!B$2:B$746)). And it does. The first returns an array of 1/0, whereas the latter returns an array of TRUE/FALSE, so they are not the same. In the same way, --(MONTH('2008'!B$2:B$746)=$I4) returns an array of 1/0, whereas (MONTH('2008'!B$2:B$746)=$I4)) returns an array of TRUE/FALSE. The double unary is used to coerce arrays of TRUE/FALSE to corresponding arrays of 1/0, which the PRODUCT part of SUMPRODUCT does its work on. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidK" wrote in message ... Bob, I read the article, which identifies the use of the double unary. I even understand what the double unary is. What I do not understand is the function of the double unary in the formula: =SUMPRODUCT(--(ISNUMBER('2008'!B$2:B$746)),--(MONTH('2008'!B$2:B$746)= $I4)) Maybe my understanding is flawed. I see this as creating an array of 0's for every blank cell in B2:B746 and 1's where there is a numeric value(date being stored as a serialized number). Therefore, (--(ISNUMBER('2008'!B$2:B$746)) should work as well as ((ISNUMBER('2008'!B$2:B$746)). And it does. What confuses me is the second array. --(MONTH('2008'!B$2:B$746)=$I4) should do the same, creating a 1 or 0 to be multiplied by the corresponding value from the ISNUMBER array. I do not see why (MONTH('2008'!B$2:B$746)=$I4)) does not produce the same result without the operator. Is it because it is a logical value versus numeric? Regardless, thank you for the introduction to SUMPRODUCT. David |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula acting as it shuld ... but
On Jan 3, 6:30*am, "Bob Phillips" wrote:
I don't understand what you mean by the statement ... Therefore, (--(ISNUMBER('2008'!B$2:B$746)) should work as well as ((ISNUMBER('2008'!B$2:B$746)). *And it does. The first returns an array of 1/0, whereas the latter returns an array of TRUE/FALSE, so they are not the same. In the same way, --(MONTH('2008'!B$2:B$746)=$I4) returns an array of 1/0, whereas *(MONTH('2008'!B$2:B$746)=$I4)) returns an array of TRUE/FALSE.. The double unary is used to coerce arrays of TRUE/FALSE to corresponding arrays of 1/0, which the PRODUCT part of SUMPRODUCT does its work on. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidK" wrote in message ... Bob, I read the article, which identifies the use of the double unary. *I even understand what the double unary is. What I do not understand is the function of the double unary in the formula: =SUMPRODUCT(--(ISNUMBER('2008'!B$2:B$746)),--(MONTH('2008'!B$2:B$746)= $I4)) Maybe my understanding is flawed. I see this as creating an array of 0's for every blank cell in B2:B746 and 1's where there is a numeric value(date being stored as a serialized number). Therefore, (--(ISNUMBER('2008'!B$2:B$746)) should work as well as ((ISNUMBER('2008'!B$2:B$746)). *And it does. What confuses me is the second array. *--(MONTH('2008'!B$2:B$746)=$I4) should do the same, creating a 1 or 0 to be multiplied by the corresponding value from the ISNUMBER array. *I do not see why (MONTH('2008'!B$2:B$746)=$I4)) does not produce the same result without the operator. Is it because it is a logical value versus numeric? Regardless, thank you for the introduction to SUMPRODUCT. David- Hide quoted text - - Show quoted text - Much thanks for clarifying. Great lesson. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wat formula shuld i use to find the details in the cell? | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
Formula acting as a string | Excel Discussion (Misc queries) | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |