| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
maybe lookup/index/match/sumproduct
happy new year, Sirs,
i am looking again for another genius to help me... please help me to find any formula to solve this PROGressive count for my2007 workbook. i need to count in a cumulative manner the mid-match of 2 column-row data in COL.A and COL.B. ----------------------------------------------- Table 1: Reference for lookup/ mid-match COL.A COL.B COL.C COL.D b 2 d 4 f 6 a 8 c 10 ------------------------------------------------------------------------------------------ Table 2: Value to lookup for mid-match A B C D a 1 =count = 0 b 2 =count = 0 c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4] d 4 =count = 1 e 5 1 =count = 2 [ cum. sum] f 6 =count = 2 g 7 1 =count = 3 a 8 =count = 3 b 9 1 =count = 4 c 10 =count = 4 d 11 =count = 4 e 12 =count = 4 f 13 =count = 4 g 14 =count = 4 need a formula, to replace the formulas of Col.D without a helper column COL.C. Note Col.C is either 1 or a blank result. Table 2 Col.A is a looping series from a to g. Col.B filled with a series of numeric or date value. thanks for reply with any smart or long formula or whatever merged formulas. best regards, been dribled to 2007 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
maybe lookup/index/match/sumproduct
Hi
I'm far from a genius but maybe something like this: assuming A5:B11 is your first table and A16 is the first cell in your 2nd table =SUMPRODUCT((VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a ","b","g"},{6,-1,-6})),$A$5:$B$11,2,0)=B16-1)*(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g" },{6,1,-6})),$A$5:$B$11,2,0)=B16+1)) copy down 1 cell and add the previous cell result to the end of the formula then copy down as far as needed. HTH Jean-Guy "been dribbled to 2007" wrote: happy new year, Sirs, i am looking again for another genius to help me... please help me to find any formula to solve this PROGressive count for my2007 workbook. i need to count in a cumulative manner the mid-match of 2 column-row data in COL.A and COL.B. ----------------------------------------------- Table 1: Reference for lookup/ mid-match COL.A COL.B COL.C COL.D b 2 d 4 f 6 a 8 c 10 ------------------------------------------------------------------------------------------ Table 2: Value to lookup for mid-match A B C D a 1 =count = 0 b 2 =count = 0 c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4] d 4 =count = 1 e 5 1 =count = 2 [ cum. sum] f 6 =count = 2 g 7 1 =count = 3 a 8 =count = 3 b 9 1 =count = 4 c 10 =count = 4 d 11 =count = 4 e 12 =count = 4 f 13 =count = 4 g 14 =count = 4 need a formula, to replace the formulas of Col.D without a helper column COL.C. Note Col.C is either 1 or a blank result. Table 2 Col.A is a looping series from a to g. Col.B filled with a series of numeric or date value. thanks for reply with any smart or long formula or whatever merged formulas. best regards, been dribled to 2007 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
maybe lookup/index/match/sumproduct
thank you i have seen results (0 and #N/A), yet not so quite sure if i does
it correctly the table i gave is a sample its my first time to see such formula, hence its not easy to understand it... LOOKUP(A16,{"a","b","g"},{6,-1,-6})) . LOOKUP(A16,{"a","b","g"},{6,1,-6}) what does the above do? 2 lookups <first is "-1" and the next is "1"... and how to avoid #N/A result.. thank you for your quick response, dribler2 "pinmaster" wrote: Hi I'm far from a genius but maybe something like this: assuming A5:B11 is your first table and A16 is the first cell in your 2nd table =SUMPRODUCT((VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a ","b","g"},{6,-1,-6})),$A$5:$B$11,2,0)=B16-1)*(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g" },{6,1,-6})),$A$5:$B$11,2,0)=B16+1)) copy down 1 cell and add the previous cell result to the end of the formula then copy down as far as needed. HTH Jean-Guy "been dribbled to 2007" wrote: happy new year, Sirs, i am looking again for another genius to help me... please help me to find any formula to solve this PROGressive count for my2007 workbook. i need to count in a cumulative manner the mid-match of 2 column-row data in COL.A and COL.B. ----------------------------------------------- Table 1: Reference for lookup/ mid-match COL.A COL.B COL.C COL.D b 2 d 4 f 6 a 8 c 10 ------------------------------------------------------------------------------------------ Table 2: Value to lookup for mid-match A B C D a 1 =count = 0 b 2 =count = 0 c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4] d 4 =count = 1 e 5 1 =count = 2 [ cum. sum] f 6 =count = 2 g 7 1 =count = 3 a 8 =count = 3 b 9 1 =count = 4 c 10 =count = 4 d 11 =count = 4 e 12 =count = 4 f 13 =count = 4 g 14 =count = 4 need a formula, to replace the formulas of Col.D without a helper column COL.C. Note Col.C is either 1 or a blank result. Table 2 Col.A is a looping series from a to g. Col.B filled with a series of numeric or date value. thanks for reply with any smart or long formula or whatever merged formulas. best regards, been dribled to 2007 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
maybe lookup/index/match/sumproduct
Hi,
Please disregard my first post, there was some problem with the formula. Try this instead: =IF(ISNA(SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))),0,SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))) again copy down one cell and add the cell above it to the end of the formula, you can also replace the 0 in the middle with the cell above: e.g....first formula is in D16 then in D17 =IF(ISNA(SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))),D16,SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0))))+D16 adjust to suit. HTH Jean-Guy "been dribbled to 2007" wrote: happy new year, Sirs, i am looking again for another genius to help me... please help me to find any formula to solve this PROGressive count for my2007 workbook. i need to count in a cumulative manner the mid-match of 2 column-row data in COL.A and COL.B. ----------------------------------------------- Table 1: Reference for lookup/ mid-match COL.A COL.B COL.C COL.D b 2 d 4 f 6 a 8 c 10 ------------------------------------------------------------------------------------------ Table 2: Value to lookup for mid-match A B C D a 1 =count = 0 b 2 =count = 0 c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4] d 4 =count = 1 e 5 1 =count = 2 [ cum. sum] f 6 =count = 2 g 7 1 =count = 3 a 8 =count = 3 b 9 1 =count = 4 c 10 =count = 4 d 11 =count = 4 e 12 =count = 4 f 13 =count = 4 g 14 =count = 4 need a formula, to replace the formulas of Col.D without a helper column COL.C. Note Col.C is either 1 or a blank result. Table 2 Col.A is a looping series from a to g. Col.B filled with a series of numeric or date value. thanks for reply with any smart or long formula or whatever merged formulas. best regards, been dribled to 2007 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
maybe lookup/index/match/sumproduct
Hi,
The #N/A is cause when a letter in the second table is not present in the first, my last formula should take care of that. as for: LOOKUP(A16,{"a","b","g"},{6,-1,-6})) . LOOKUP(A16,{"a","b","g"},{6,1,-6}) I had parts of this wrong, again the last formula I gave should resolve this. Each letter has its own code, for ex. the letter "a" is 97, "b" is 98 and so on....and this tell excel how much to add or deduct from the code depending on the letter it is looking at,and the CHAR function turns those codes into letters. As I said, I'm not an expert so there may be a better solution. HTH Jean-Guy "been dribbled to 2007" wrote: thank you i have seen results (0 and #N/A), yet not so quite sure if i does it correctly the table i gave is a sample its my first time to see such formula, hence its not easy to understand it... LOOKUP(A16,{"a","b","g"},{6,-1,-6})) . LOOKUP(A16,{"a","b","g"},{6,1,-6}) what does the above do? 2 lookups <first is "-1" and the next is "1"... and how to avoid #N/A result.. thank you for your quick response, dribler2 "pinmaster" wrote: Hi I'm far from a genius but maybe something like this: assuming A5:B11 is your first table and A16 is the first cell in your 2nd table =SUMPRODUCT((VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a ","b","g"},{6,-1,-6})),$A$5:$B$11,2,0)=B16-1)*(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g" },{6,1,-6})),$A$5:$B$11,2,0)=B16+1)) copy down 1 cell and add the previous cell result to the end of the formula then copy down as far as needed. HTH Jean-Guy "been dribbled to 2007" wrote: happy new year, Sirs, i am looking again for another genius to help me... please help me to find any formula to solve this PROGressive count for my2007 workbook. i need to count in a cumulative manner the mid-match of 2 column-row data in COL.A and COL.B. ----------------------------------------------- Table 1: Reference for lookup/ mid-match COL.A COL.B COL.C COL.D b 2 d 4 f 6 a 8 c 10 ------------------------------------------------------------------------------------------ Table 2: Value to lookup for mid-match A B C D a 1 =count = 0 b 2 =count = 0 c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4] d 4 =count = 1 e 5 1 =count = 2 [ cum. sum] f 6 =count = 2 g 7 1 =count = 3 a 8 =count = 3 b 9 1 =count = 4 c 10 =count = 4 d 11 =count = 4 e 12 =count = 4 f 13 =count = 4 g 14 =count = 4 need a formula, to replace the formulas of Col.D without a helper column COL.C. Note Col.C is either 1 or a blank result. Table 2 Col.A is a looping series from a to g. Col.B filled with a series of numeric or date value. thanks for reply with any smart or long formula or whatever merged formulas. best regards, been dribled to 2007 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
maybe lookup/index/match/sumproduct
almost ,Sir
yet i need to clarify that need a formula, to replace the formulas of Col.D without a helper column COL.C. please add..."i need a single formula, without building a list under Col D. this may look like a calculator...". ...in mySheet1: The Col.A and Col.B <10000 rows has a value in series and its hidden. Due to a long list of values which will be fed intermittently with data, i need to avoid long calculation time. the user will just feed in two data "c and 1460" and the formula must be able to gave the cumulative sum. hope that your formula can be a little bit adjusted to suit my request. thanks again dribler2 "pinmaster" wrote: Hi, Please disregard my first post, there was some problem with the formula. Try this instead: =IF(ISNA(SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))),0,SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))) again copy down one cell and add the cell above it to the end of the formula, you can also replace the 0 in the middle with the cell above: e.g....first formula is in D16 then in D17 =IF(ISNA(SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))),D16,SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0))))+D16 adjust to suit. HTH Jean-Guy "been dribbled to 2007" wrote: happy new year, Sirs, i am looking again for another genius to help me... please help me to find any formula to solve this PROGressive count for my2007 workbook. i need to count in a cumulative manner the mid-match of 2 column-row data in COL.A and COL.B. ----------------------------------------------- Table 1: Reference for lookup/ mid-match COL.A COL.B COL.C COL.D b 2 d 4 f 6 a 8 c 10 ------------------------------------------------------------------------------------------ Table 2: Value to lookup for mid-match A B C D a 1 =count = 0 b 2 =count = 0 c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4] d 4 =count = 1 e 5 1 =count = 2 [ cum. sum] f 6 =count = 2 g 7 1 =count = 3 a 8 =count = 3 b 9 1 =count = 4 c 10 =count = 4 d 11 =count = 4 e 12 =count = 4 f 13 =count = 4 g 14 =count = 4 need a formula, to replace the formulas of Col.D without a helper column COL.C. Note Col.C is either 1 or a blank result. Table 2 Col.A is a looping series from a to g. Col.B filled with a series of numeric or date value. thanks for reply with any smart or long formula or whatever merged formulas. best regards, been dribled to 2007 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
maybe lookup/index/match/sumproduct
Hi
Sorry to say but that is beyond my expertise, I'm sure some people are already working on that so be patient and good luck. Regards! Jean-Guy "been dribbled to 2007" wrote: almost ,Sir yet i need to clarify that need a formula, to replace the formulas of Col.D without a helper column COL.C. please add..."i need a single formula, without building a list under Col D. this may look like a calculator...". ..in mySheet1: The Col.A and Col.B <10000 rows has a value in series and its hidden. Due to a long list of values which will be fed intermittently with data, i need to avoid long calculation time. the user will just feed in two data "c and 1460" and the formula must be able to gave the cumulative sum. hope that your formula can be a little bit adjusted to suit my request. thanks again dribler2 "pinmaster" wrote: Hi, Please disregard my first post, there was some problem with the formula. Try this instead: =IF(ISNA(SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))),0,SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))) again copy down one cell and add the cell above it to the end of the formula, you can also replace the 0 in the middle with the cell above: e.g....first formula is in D16 then in D17 =IF(ISNA(SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0)))),D16,SUMPRODUCT(--(VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{ 6,1,-6})),$A$5:$B$11,2,0)-B16=B16-VLOOKUP(CHAR(CODE(A16)+LOOKUP(A16,{"a","b","g"},{6 ,-1,-1})),$A$5:$B$11,2,0))))+D16 adjust to suit. HTH Jean-Guy "been dribbled to 2007" wrote: happy new year, Sirs, i am looking again for another genius to help me... please help me to find any formula to solve this PROGressive count for my2007 workbook. i need to count in a cumulative manner the mid-match of 2 column-row data in COL.A and COL.B. ----------------------------------------------- Table 1: Reference for lookup/ mid-match COL.A COL.B COL.C COL.D b 2 d 4 f 6 a 8 c 10 ------------------------------------------------------------------------------------------ Table 2: Value to lookup for mid-match A B C D a 1 =count = 0 b 2 =count = 0 c 3 1 =count = 1 [c,3] b/w [b,2] & [d,4] d 4 =count = 1 e 5 1 =count = 2 [ cum. sum] f 6 =count = 2 g 7 1 =count = 3 a 8 =count = 3 b 9 1 =count = 4 c 10 =count = 4 d 11 =count = 4 e 12 =count = 4 f 13 =count = 4 g 14 =count = 4 need a formula, to replace the formulas of Col.D without a helper column COL.C. Note Col.C is either 1 or a blank result. Table 2 Col.A is a looping series from a to g. Col.B filled with a series of numeric or date value. thanks for reply with any smart or long formula or whatever merged formulas. best regards, been dribled to 2007 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
maybe lookup/index/match/sumproduct
thank you Sir Jean-Guy
"pinmaster" wrote: Hi Sorry to say but that is beyond my expertise, I'm sure some people are already working on that so be patient and good luck. Regards! Jean-Guy "been dribbled to 2007" wrote: almost ,Sir yet i need to clarify that need a formula, to replace the formulas of Col.D without a helper column COL.C. please add..."i need a single formula, without building a list under Col D. this may look like a calculator...". ..in mySheet1: The Col.A and Col.B <10000 rows has a value in series and its hidden. Due to a long list of values which will be fed intermittently with data, i need to avoid long calculation time. the user will just feed in two data "c and 1460" and the formula must be able to gave the cumulative sum. hope that your formula can be a little bit adjusted to suit my request. thanks again dribler2 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
