Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with array formula
It has been years since I've done an array formula. And I was never that
good with them. I would think that this should work: {=SUM((LEFT(A1:A35000,3)<"HD:")*(C1:C35000))} It doesn't. It gets #VALUE! Don. www.donwiss.com (e-mail link at home page bottom). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with array formula
hi Don,
=SUMPRODUCT((LEFT(A1:A35000,3)<"HD:")*(C1:C35000) ) isabelle Le 2014-03-10 21:16, Don Wiss a écrit : It has been years since I've done an array formula. And I was never that good with them. I would think that this should work: {=SUM((LEFT(A1:A35000,3)<"HD:")*(C1:C35000))} It doesn't. It gets #VALUE! Don. www.donwiss.com (e-mail link at home page bottom). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with array formula
On Mon, 10 Mar 2014 21:36:29 -0400, isabelle wrote:
Le 2014-03-10 21:16, Don Wiss a écrit : It has been years since I've done an array formula. And I was never that good with them. I would think that this should work: {=SUM((LEFT(A1:A35000,3)<"HD:")*(C1:C35000))} It doesn't. It gets #VALUE! =SUMPRODUCT((LEFT(A1:A35000,3)<"HD:")*(C1:C35000 )) Nope. I think the problem has to do with many of the cells in C1:C35000 are non-numeric. I tried this: {=SUM(AND(LEFT(A1:A35000,3)<"HD:",ISNUMBER(C1:C35 000))*C1:C35000)} And still n.g. Don. www.donwiss.com (e-mail link at home page bottom). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with array formula
"Don Wiss" wrote:
I think the problem has to do with many of the cells in C1:C35000 are non-numeric. I tried this: {=SUM(AND(LEFT(A1:A35000,3)<"HD:",ISNUMBER(C1:C35 000))*C1:C35000)} And still n.g. The following normally-entered formula (just press Enter as usual) should work: =SUMPRODUCT(--(LEFT(A1:A35000,3)<"HD:"),C1:C35000) The double-negate (--) converts TRUE and FALSE to 1 and 0, which SUMPRODUCT requires to work as intended. It is not necessary to test ISNUMBER(C1:C35000) because SUMPRODUCT treats text and logic values in C1:C35000 as if they were zero. For future purposes, alternatively, the following array-entered formula (press ctrl+shift+Enter instead of just Enter) should also work: =SUM(IF(LEFT(A1:A35000,3)<"HD:",IF(ISNUMBER(C1:C3 5000),C1:C35000))) That works because of the left-to-right processing that the IF pseudo-function does. We cannot use AND(LEFT(A1:A35000,3)<"HD:",ISNUMBER(C1:C35000) in array formulas because the AND function itself processes the entire arguments, instead of processing them row by row as intended. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with array formula
On Mon, 10 Mar 2014 23:33:50 -0700, "joeu2004" wrote:
Don Wiss wrote: I think the problem has to do with many of the cells in C1:C35000 are non-numeric. I tried this: {=SUM(AND(LEFT(A1:A35000,3)<"HD:",ISNUMBER(C1:C35 000))*C1:C35000)} And still n.g. The following normally-entered formula (just press Enter as usual) should work: =SUMPRODUCT(--(LEFT(A1:A35000,3)<"HD:"),C1:C35000) For future purposes, alternatively, the following array-entered formula (press ctrl+shift+Enter instead of just Enter) should also work: =SUM(IF(LEFT(A1:A35000,3)<"HD:",IF(ISNUMBER(C1:C 35000),C1:C35000))) Both work just fine. I used the first one. Thanks. I would also like to get a count. I used this array formula for that: {=SUM(IF(LEFT(A1:A35000,3)<"HD:",IF(ISNUMBER(C1:C 35000),1)))} Don. www.donwiss.com (e-mail link at home page bottom). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with array formula
"Don Wiss" wrote:
"joeu2004" wrote: =SUMPRODUCT(--(LEFT(A1:A35000,3)<"HD:"),C1:C35000) [....] I would also like to get a count. I used this array formula for that: {=SUM(IF(LEFT(A1:A35000,3)<"HD:",IF(ISNUMBER(C1:C 35000),1)))} Alternatively, the following normally-entered formula (just press Enter as usual): =SUMPRODUCT((LEFT(A1:A35000,3)<"HD:")*ISNUMBER(C1 :C35000)) Previously, I noted the use of double-negate (--) to convert TRUE and FALSE into 1 and 0. I should have noted that any arithmetic operation accomplishes the same thing. That is why it is sufficient to just multiply the two logic results; no need to also double-negate. I avoid array-entered formulas whenever reasonable because they are tedious to use and error-prone. We must always remember to press ctrl+shift+Enter after editing. If we forget and press just Enter instead, the formula does __not__ return an error in some contexts, leading to incorrect results. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with a Array formula, Please help !! | Excel Worksheet Functions | |||
Problem w/Array Formula | Excel Worksheet Functions | |||
Array Formula Problem | Excel Worksheet Functions | |||
problem with Array Formula | Excel Worksheet Functions | |||
formula array problem | Excel Programming |