|
HELP! - Formula suggestion
Dear Experts,
Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), ... ... ... Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
HELP! - Formula suggestion
Will any numbers be repeated on any single row:
Row 1 = 2,1,7,10,8,5 Row 2 = 5,10,11,12,20,1 Row 3 = 15,10,16,17,18,1 Is this possible: Row 1 = 1,1,1,10,10,5 Based on you sample which doesn't have any repeats per row: ...........A..........B 10......5..........10 11......1..........10 Enter this formula in C10: =SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2)) Copy down to C11 Biff "Will" wrote in message ... Dear Experts, Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), .. .. .. Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
HELP! - Formula suggestion
Actually, we can remove one of the arrays:
=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A$1:F$3,A10:B10,0))),{1;1;1;1;1;1} )=2)) Biff "T. Valko" wrote in message ... Will any numbers be repeated on any single row: Row 1 = 2,1,7,10,8,5 Row 2 = 5,10,11,12,20,1 Row 3 = 15,10,16,17,18,1 Is this possible: Row 1 = 1,1,1,10,10,5 Based on you sample which doesn't have any repeats per row: ..........A..........B 10......5..........10 11......1..........10 Enter this formula in C10: =SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2)) Copy down to C11 Biff "Will" wrote in message ... Dear Experts, Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), .. .. .. Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
HELP! - Formula suggestion
Valko,
No numbers will repeat in a row.. I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to say.. thanks.. Can briefly explain the formula? esp the {1,1,1,1} part... "T. Valko" wrote: Will any numbers be repeated on any single row: Row 1 = 2,1,7,10,8,5 Row 2 = 5,10,11,12,20,1 Row 3 = 15,10,16,17,18,1 Is this possible: Row 1 = 1,1,1,10,10,5 Based on you sample which doesn't have any repeats per row: ...........A..........B 10......5..........10 11......1..........10 Enter this formula in C10: =SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2)) Copy down to C11 Biff "Will" wrote in message ... Dear Experts, Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), .. .. .. Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
HELP! - Formula suggestion
You're welcome. Thanks for the feedback!
For an explanation lets use a very small sample: ......A.....B.....C 1...1......5.....10 You want to count how many times 5 and 10 appear on the same row. A10 = 5 B10 = 10 =SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2)) This portion of the formula tests to see if any numbers in A1:C1 match the numbers in A10:B10 ISNUMBER(MATCH(A1:C1,A10:B10,0)) This will return a horizontal array of either TRUE or FALSE FALSE TRUE TRUE We need to convert those logical values to numbers. To do that we use the double unary: --(ISNUMBER(MATCH(A1:C1,A10:B10,0)) That will convert TRUE to 1 and FALSE to 0: 0 1 1 MMULT (matrix multiplication) is then used to return the count of matches per row. We multiply the horizontal array 0 1 1 by a vertical array equal to the number of columns in the data set. In this case we have 3 columns A1:C1, so the vertical array is {1;1;1} It would look something like this: 0..1..1.......1 ..................1 ..................1 The 0 times the top vertical 1 The middle horizontal 1 times the middle vertical 1 The rightmost horizontal 1 times the bottom vertical 1 The result would be: 0*1 + 1*1 + 1*1 0 + 1 + 1 = 2 Now, imagine your sample with 3 rows of data. The MMULT function would return a count like that above for each row. Those counts are then passed to the SUMPRODUCT function where they are tested to see if they equal 2 (for 2 matches: A1:C1 matches A10 and A1:C1 matches B10). This is also an array of TRUE or FALSE. Then we once again convert those logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the final result of 1 (based on this explanation sample). Biff "Will" wrote in message ... Valko, No numbers will repeat in a row.. I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to say.. thanks.. Can briefly explain the formula? esp the {1,1,1,1} part... "T. Valko" wrote: Will any numbers be repeated on any single row: Row 1 = 2,1,7,10,8,5 Row 2 = 5,10,11,12,20,1 Row 3 = 15,10,16,17,18,1 Is this possible: Row 1 = 1,1,1,10,10,5 Based on you sample which doesn't have any repeats per row: ...........A..........B 10......5..........10 11......1..........10 Enter this formula in C10: =SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2)) Copy down to C11 Biff "Will" wrote in message ... Dear Experts, Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), .. .. .. Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
HELP! - Formula suggestion
Thanx so so much
I understn now. "T. Valko" wrote: You're welcome. Thanks for the feedback! For an explanation lets use a very small sample: ......A.....B.....C 1...1......5.....10 You want to count how many times 5 and 10 appear on the same row. A10 = 5 B10 = 10 =SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2)) This portion of the formula tests to see if any numbers in A1:C1 match the numbers in A10:B10 ISNUMBER(MATCH(A1:C1,A10:B10,0)) This will return a horizontal array of either TRUE or FALSE FALSE TRUE TRUE We need to convert those logical values to numbers. To do that we use the double unary: --(ISNUMBER(MATCH(A1:C1,A10:B10,0)) That will convert TRUE to 1 and FALSE to 0: 0 1 1 MMULT (matrix multiplication) is then used to return the count of matches per row. We multiply the horizontal array 0 1 1 by a vertical array equal to the number of columns in the data set. In this case we have 3 columns A1:C1, so the vertical array is {1;1;1} It would look something like this: 0..1..1.......1 ..................1 ..................1 The 0 times the top vertical 1 The middle horizontal 1 times the middle vertical 1 The rightmost horizontal 1 times the bottom vertical 1 The result would be: 0*1 + 1*1 + 1*1 0 + 1 + 1 = 2 Now, imagine your sample with 3 rows of data. The MMULT function would return a count like that above for each row. Those counts are then passed to the SUMPRODUCT function where they are tested to see if they equal 2 (for 2 matches: A1:C1 matches A10 and A1:C1 matches B10). This is also an array of TRUE or FALSE. Then we once again convert those logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the final result of 1 (based on this explanation sample). Biff "Will" wrote in message ... Valko, No numbers will repeat in a row.. I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to say.. thanks.. Can briefly explain the formula? esp the {1,1,1,1} part... "T. Valko" wrote: Will any numbers be repeated on any single row: Row 1 = 2,1,7,10,8,5 Row 2 = 5,10,11,12,20,1 Row 3 = 15,10,16,17,18,1 Is this possible: Row 1 = 1,1,1,10,10,5 Based on you sample which doesn't have any repeats per row: ...........A..........B 10......5..........10 11......1..........10 Enter this formula in C10: =SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2)) Copy down to C11 Biff "Will" wrote in message ... Dear Experts, Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), .. .. .. Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
HELP! - Formula suggestion
"Will" wrote: Valko, No numbers will repeat in a row.. I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to say.. thanks.. Can briefly explain the formula? esp the {1,1,1,1} part... "T. Valko" wrote: Will any numbers be repeated on any single row: Row 1 = 2,1,7,10,8,5 Row 2 = 5,10,11,12,20,1 Row 3 = 15,10,16,17,18,1 Is this possible: Row 1 = 1,1,1,10,10,5 Based on you sample which doesn't have any repeats per row: ...........A..........B 10......5..........10 11......1..........10 Enter this formula in C10: =SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2)) Copy down to C11 Biff "Will" wrote in message ... Dear Experts, Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), .. .. .. Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
HELP! - Formula suggestion
Chalie, i like the way you try to solve problems for others. i like u!
gorro "T. Valko" wrote: You're welcome. Thanks for the feedback! For an explanation lets use a very small sample: ......A.....B.....C 1...1......5.....10 You want to count how many times 5 and 10 appear on the same row. A10 = 5 B10 = 10 =SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2)) This portion of the formula tests to see if any numbers in A1:C1 match the numbers in A10:B10 ISNUMBER(MATCH(A1:C1,A10:B10,0)) This will return a horizontal array of either TRUE or FALSE FALSE TRUE TRUE We need to convert those logical values to numbers. To do that we use the double unary: --(ISNUMBER(MATCH(A1:C1,A10:B10,0)) That will convert TRUE to 1 and FALSE to 0: 0 1 1 MMULT (matrix multiplication) is then used to return the count of matches per row. We multiply the horizontal array 0 1 1 by a vertical array equal to the number of columns in the data set. In this case we have 3 columns A1:C1, so the vertical array is {1;1;1} It would look something like this: 0..1..1.......1 ..................1 ..................1 The 0 times the top vertical 1 The middle horizontal 1 times the middle vertical 1 The rightmost horizontal 1 times the bottom vertical 1 The result would be: 0*1 + 1*1 + 1*1 0 + 1 + 1 = 2 Now, imagine your sample with 3 rows of data. The MMULT function would return a count like that above for each row. Those counts are then passed to the SUMPRODUCT function where they are tested to see if they equal 2 (for 2 matches: A1:C1 matches A10 and A1:C1 matches B10). This is also an array of TRUE or FALSE. Then we once again convert those logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the final result of 1 (based on this explanation sample). Biff "Will" wrote in message ... Valko, No numbers will repeat in a row.. I tried the formula given by u... Its SOLVED... hooray.. I dunno wat to say.. thanks.. Can briefly explain the formula? esp the {1,1,1,1} part... "T. Valko" wrote: Will any numbers be repeated on any single row: Row 1 = 2,1,7,10,8,5 Row 2 = 5,10,11,12,20,1 Row 3 = 15,10,16,17,18,1 Is this possible: Row 1 = 1,1,1,10,10,5 Based on you sample which doesn't have any repeats per row: ...........A..........B 10......5..........10 11......1..........10 Enter this formula in C10: =SUMPRODUCT(--(MMULT((ISNUMBER(MATCH(A$1:F$3,A10,0)))+(ISNUMBER( MATCH(A$1:F$3,B10,0))),{1;1;1;1;1;1})=2)) Copy down to C11 Biff "Will" wrote in message ... Dear Experts, Which Formula shd I use so as to compute how many times a certain pair of numbers appeared in a few groups of 6 numbers example 1st group of 6 numbers: 2 (A1), 1 (B1), 7 (C1), 10 (D1), 8 (E1), 5 (F1) 2nd group of 6 numbers: 5 (A2), 10 (B2), 11 (C2), 12 (D2), 20 (E2), 1 (F2), 3rd group of 6numbers: 15 (A3), 10 (B3), 16 (C3), 17 (D3), 18 (E3), 1 (F3), .. .. .. Number (5 & 10) appear: 2 times Number (1 & 10) appear: 3 times I tried to use countif but it only enable me to match a Single range with a Single citeria but now i have multiple Range to match with 2 citeria. |
| All times are GMT +1. The time now is 01:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com