| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Formula into Array
Thanks for looking at this. I have checked through the help (F1) but somehow
or rather, just can't find the solution: What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1,DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
" wrote...
.... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Harlan,
It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Correction;
From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
In other words, you want to use a dynamic range. You would have to do the
same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Valko,
Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
One thing you could do is put the VLOOKUP in another cell and then refer to
that cell: A1: =VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE) Then: =SUMPRODUCT(--(Dbase!$K$1:$K$5450=A1),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Another way to create dynamic ranges (assumes no empty cells within the range): =A1:INDEX(A:A,COUNTA(A:A)) There is virtually no meaningful difference in calc times using this method vs. using the OFFSET method (offset is actually a pretty fast function) but the INDEX method is significantly faster on recalcs. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
P.S. - this is pretty much the definitive source for speed issues:
http://www.decisionmodels.com/ Biff Microsoft Excel MVP "T. Valko" wrote in message ... One thing you could do is put the VLOOKUP in another cell and then refer to that cell: A1: =VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE) Then: =SUMPRODUCT(--(Dbase!$K$1:$K$5450=A1),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Another way to create dynamic ranges (assumes no empty cells within the range): =A1:INDEX(A:A,COUNTA(A:A)) There is virtually no meaningful difference in calc times using this method vs. using the OFFSET method (offset is actually a pretty fast function) but the INDEX method is significantly faster on recalcs. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Valko,
This is a weakness of sumproduct (i think!) in terms of data range, coz when I use sumif, it allows (K:K) full range with minor twitches to the Dbase (concatenate the criterias). I will change it to sumif. I was trying out the sumproduct formula, I guess the ranging of data is rather restrictive. If I use sumif, I will need to concatenate(K & H) in the Dbase. Thanks for the help!!! "T. Valko" wrote: P.S. - this is pretty much the definitive source for speed issues: http://www.decisionmodels.com/ Biff Microsoft Excel MVP "T. Valko" wrote in message ... One thing you could do is put the VLOOKUP in another cell and then refer to that cell: A1: =VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE) Then: =SUMPRODUCT(--(Dbase!$K$1:$K$5450=A1),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Another way to create dynamic ranges (assumes no empty cells within the range): =A1:INDEX(A:A,COUNTA(A:A)) There is virtually no meaningful difference in calc times using this method vs. using the OFFSET method (offset is actually a pretty fast function) but the INDEX method is significantly faster on recalcs. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
This is a weakness of sumproduct (i think!) in terms of data range,
coz when I use sumif, it allows (K:K) full range with minor twitches to the Dbase It's not just SUMPRODUCT, it's any array formula. Although you don't have to array enter SUMPRODUCT it still calculates like an array entered formula. Array formulas calculate *every* cell that's referenced. A function like SUMIF only calculates the used range. So, if you have data in A1:A100 and have this: =SUMIF(A:A,"0"), it only calculates A1:A100. A101 to the bottom of the column are ignored. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, This is a weakness of sumproduct (i think!) in terms of data range, coz when I use sumif, it allows (K:K) full range with minor twitches to the Dbase (concatenate the criterias). I will change it to sumif. I was trying out the sumproduct formula, I guess the ranging of data is rather restrictive. If I use sumif, I will need to concatenate(K & H) in the Dbase. Thanks for the help!!! "T. Valko" wrote: P.S. - this is pretty much the definitive source for speed issues: http://www.decisionmodels.com/ Biff Microsoft Excel MVP "T. Valko" wrote in message ... One thing you could do is put the VLOOKUP in another cell and then refer to that cell: A1: =VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE) Then: =SUMPRODUCT(--(Dbase!$K$1:$K$5450=A1),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Another way to create dynamic ranges (assumes no empty cells within the range): =A1:INDEX(A:A,COUNTA(A:A)) There is virtually no meaningful difference in calc times using this method vs. using the OFFSET method (offset is actually a pretty fast function) but the INDEX method is significantly faster on recalcs. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
"T. Valko" wrote...
.... It's not just SUMPRODUCT, it's any array formula. Although you don't have to array enter SUMPRODUCT it still calculates like an array entered formula. .... Overly broad, or would you like to take a stab at explaining why =SUMPRODUCT(ROW(A:A)) returns 2147516416 (= 65537 * 32768) as expected? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
"Harlan Grove" wrote in message ... "T. Valko" wrote... ... It's not just SUMPRODUCT, it's any array formula. Although you don't have to array enter SUMPRODUCT it still calculates like an array entered formula. ... Overly broad, or would you like to take a stab at explaining why =SUMPRODUCT(ROW(A:A)) returns 2147516416 (= 65537 * 32768) as expected? Not sure what you're leading to, but: 1+2+3+4+5+6+7........+65536 = 2147516416 Biff Microsoft Excel MVP |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Valko,
Last Question: =TYPE(CONCATENATE("Dbase!$K$1:","$K$",COUNTA(Dbase !A:A))) equals to 2 or text I think if I can convert it to array, 64, then the formula should work. Is there a way to convert it? "T. Valko" wrote: This is a weakness of sumproduct (i think!) in terms of data range, coz when I use sumif, it allows (K:K) full range with minor twitches to the Dbase It's not just SUMPRODUCT, it's any array formula. Although you don't have to array enter SUMPRODUCT it still calculates like an array entered formula. Array formulas calculate *every* cell that's referenced. A function like SUMIF only calculates the used range. So, if you have data in A1:A100 and have this: =SUMIF(A:A,"0"), it only calculates A1:A100. A101 to the bottom of the column are ignored. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, This is a weakness of sumproduct (i think!) in terms of data range, coz when I use sumif, it allows (K:K) full range with minor twitches to the Dbase (concatenate the criterias). I will change it to sumif. I was trying out the sumproduct formula, I guess the ranging of data is rather restrictive. If I use sumif, I will need to concatenate(K & H) in the Dbase. Thanks for the help!!! "T. Valko" wrote: P.S. - this is pretty much the definitive source for speed issues: http://www.decisionmodels.com/ Biff Microsoft Excel MVP "T. Valko" wrote in message ... One thing you could do is put the VLOOKUP in another cell and then refer to that cell: A1: =VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE) Then: =SUMPRODUCT(--(Dbase!$K$1:$K$5450=A1),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Another way to create dynamic ranges (assumes no empty cells within the range): =A1:INDEX(A:A,COUNTA(A:A)) There is virtually no meaningful difference in calc times using this method vs. using the OFFSET method (offset is actually a pretty fast function) but the INDEX method is significantly faster on recalcs. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Harlan,
I don't know, but the formula below won't work. Unless row 1 to 5450 or 6001 is specified. =SUMPRODUCT(--(Dbase!K:K=VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE)),--(Dbase!H:H=$B20),Dbase!F:F) Howerver, if indexmatch or vlookup, the array of K:K is acceptable, why? I'd sure like to know why too. Just for comparison, the sumif and vlookup below works for me. =SUMIF('Stock Type'!A:A,B2666,'Stock Type'!J:J) =VLOOKUP($A2669,Payment!$K:$N,2,FALSE)) This way I am looking for something like that for the sumproduct!! Thanks "Harlan Grove" wrote: "T. Valko" wrote... .... It's not just SUMPRODUCT, it's any array formula. Although you don't have to array enter SUMPRODUCT it still calculates like an array entered formula. .... Overly broad, or would you like to take a stab at explaining why =SUMPRODUCT(ROW(A:A)) returns 2147516416 (= 65537 * 32768) as expected? |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
Dear Harlan,
My apology, tot that question was for me!! I work in a multinational company but we are treated like some natives from god knows where, my computer screen changes color from red to blue, red to green or green for half a day and blinks!! Speed reading!! My apology!! |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
"T. Valko" wrote...
.... =SUMPRODUCT(ROW(A:A)) returns 2147516416 (= 65537 * 32768) as expected? Not sure what you're leading to, but: 1+2+3+4+5+6+7........+65536 = 2147516416 .... Yup. And ROW(A:A) refers to an entire column, and the SUMPRODUCT formula above works, returning the correct value. Care to explain why that works but entering =ROW() in each cell in A:A and using =SUMPRODUCT(A:A) returns #NUM!. Presumably ROW(A:A) and A:A in this case would evaluate to the same 65536-by-1 array of sequential integers from 1 to 65536. |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
"Harlan Grove" wrote in message ... "T. Valko" wrote... ... =SUMPRODUCT(ROW(A:A)) returns 2147516416 (= 65537 * 32768) as expected? Not sure what you're leading to, but: 1+2+3+4+5+6+7........+65536 = 2147516416 ... Yup. And ROW(A:A) refers to an entire column, and the SUMPRODUCT formula above works, returning the correct value. Care to explain why that works but entering =ROW() in each cell in A:A and using =SUMPRODUCT(A:A) returns #NUM!. Presumably ROW(A:A) and A:A in this case would evaluate to the same 65536-by-1 array of sequential integers from 1 to 65536. Because the entire column is being referenced by ROW and not by SUMPRODUCT. ROW returns the array of values to SUMPRODUCT. =SUMPRODUCT(A:A) evaluates the entire column as an array of references which it can't handle. For others that might be following this thread, you can use entire columns as references in array formulas as long as those references are inside nested functions that accept them. I think this is where Harlan is going with this. Here's another example: =SUMPRODUCT(SUMIF(INDIRECT("sheet"&{1,2,3}&"!A:A") ,"<Bill",INDIRECT("sheet"&{1,2,3}&"!B:B"))) The entire columns are being referenced but they're being referenced by the SUMIF function, not SUMPRODUCT. Essentially, SUMPRODUCT is calculating an array of SUMIF functions. Biff Microsoft Excel MVP |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Inserting Formula into Array
You can't "build" formula references like that. You could do it using
INDIRECT but that'll actually make things worse. You could also do it using INDEX like I showed in an earlier post but that would be no different than using a dynamic named range. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, Last Question: =TYPE(CONCATENATE("Dbase!$K$1:","$K$",COUNTA(Dbase !A:A))) equals to 2 or text I think if I can convert it to array, 64, then the formula should work. Is there a way to convert it? "T. Valko" wrote: This is a weakness of sumproduct (i think!) in terms of data range, coz when I use sumif, it allows (K:K) full range with minor twitches to the Dbase It's not just SUMPRODUCT, it's any array formula. Although you don't have to array enter SUMPRODUCT it still calculates like an array entered formula. Array formulas calculate *every* cell that's referenced. A function like SUMIF only calculates the used range. So, if you have data in A1:A100 and have this: =SUMIF(A:A,"0"), it only calculates A1:A100. A101 to the bottom of the column are ignored. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, This is a weakness of sumproduct (i think!) in terms of data range, coz when I use sumif, it allows (K:K) full range with minor twitches to the Dbase (concatenate the criterias). I will change it to sumif. I was trying out the sumproduct formula, I guess the ranging of data is rather restrictive. If I use sumif, I will need to concatenate(K & H) in the Dbase. Thanks for the help!!! "T. Valko" wrote: P.S. - this is pretty much the definitive source for speed issues: http://www.decisionmodels.com/ Biff Microsoft Excel MVP "T. Valko" wrote in message ... One thing you could do is put the VLOOKUP in another cell and then refer to that cell: A1: =VLOOKUP(DATE(YEAR($B$25),MONTH($B$25)-1,DAY(1)),Calendar,2,FALSE) Then: =SUMPRODUCT(--(Dbase!$K$1:$K$5450=A1),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Another way to create dynamic ranges (assumes no empty cells within the range): =A1:INDEX(A:A,COUNTA(A:A)) There is virtually no meaningful difference in calc times using this method vs. using the OFFSET method (offset is actually a pretty fast function) but the INDEX method is significantly faster on recalcs. Biff Microsoft Excel MVP " wrote in message ... Dear Valko, Yes, its something like that, however the Dynamic Range slows things down (real slow), that is why I limit one workbook to one or two Dynamic range only. I have tried putting 2 more dynamic range for this $F & $K column, it's a real turtle race damn slow due to calculation. Thought that since CountA would result in current range size, maybe by manipulating the array, could result in a similar result without the slow effect. Since CountA results in 6001, i have tried these stuff but it doesn't work, the array is not recognised: 1. Concatenate ("K",CountA(Dbase!A:A) result K6001 2. &"K"&Dbase!A:A also result K6001 if only can be that simple..hahaha!! --- SUMPRODUCT(--(Dbase!$K$1:&K&CountA(Dbase!A:A)=VLOOKUP And the sumproduct does not allow K:K full range. Is there another way to go around this!! Thanks. "T. Valko" wrote: In other words, you want to use a dynamic range. You would have to do the same thing for all of the ranges invloved: Dbase!$K$1:$K$5450, Dbase!$H$1:$H$5450, Dbase!$F$1:$F$5450 See this: http://contextures.com/xlNames01.html#Dynamic Biff Microsoft Excel MVP " wrote in message ... Correction; From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] How I wish it could be done this way: The Array (Dbase!K1:K[CountA(Dbase!A:A) So automatically becomes, K1:K6001 " wrote: Dear Harlan, It's not as straight forward as it seems! My posting is a little confusing. (my apology) Lets try this: From this: Original formula SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP To this: New Formula SUMPRODUCT(--(Dbase!$K$1:&F&CountA(Dbase!A:A)=VLOOKUP [something like this] I am hoping that the 5450 will be recognised with 6001, which is the new range (automatically). Thanks. "Harlan Grove" wrote: " wrote... .... What I wanna do is to substitute the 5450 with 6001 (the formula below). Coz the range has increased from 5450 to 6001. If I can slot this in, then I won't have to set the range everytime it grows. * CountA(Dbase!A:A) equals to 6001 Substiture K5450 and F5450 =SUMPRODUCT(--(Dbase!$K$1:$K$5450=VLOOKUP(DATE(YEAR($B$25),MONTH ($B$25)-1, DAY(1)),Calendar,2,FALSE)),--(Dbase!$H$1:$H$5450=$B19),Dbase!$F$1:$F$5450) Edit Replace, replacing $5450 with $6001. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| inserting hard CR in a formula | Excel Worksheet Functions | |||
| Formula for inserting blank row | Excel Worksheet Functions | |||
| mantaining formula when inserting col | Excel Discussion (Misc queries) | |||
| inserting a formula | Excel Discussion (Misc queries) | |||
| Formula changes while inserting a row !!!! | Excel Worksheet Functions | |||
Linear Mode
