Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
I really need some help to write a formula with nested functions.
How do I write a formula to calculate how many times a specific combination of values appear in two different columns. i.e. column A is populated with the values 1,2 and 3 in random order; column B is populated with values a,b and c, also in random order. What would the formula be to determine the number of times that the combination of 1 and a appear side by side on the same row; over a worksheet with 10 rows. Thanks for any help in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
Try this:
=SUMPRODUCT(--(A1:A10=1),--(B1:B10="A")) Better to use cells to hold the criteria: C1 = 1 C2 = A =SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2)) Biff "Texicanslim" wrote in message ... I really need some help to write a formula with nested functions. How do I write a formula to calculate how many times a specific combination of values appear in two different columns. i.e. column A is populated with the values 1,2 and 3 in random order; column B is populated with values a,b and c, also in random order. What would the formula be to determine the number of times that the combination of 1 and a appear side by side on the same row; over a worksheet with 10 rows. Thanks for any help in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
You need an array formula that takes performs a countif using multiple
criteria. Take a look at Chip Pearson's website. Specifically... http://www.cpearson.com/excel/array.htm -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Texicanslim" wrote: I really need some help to write a formula with nested functions. How do I write a formula to calculate how many times a specific combination of values appear in two different columns. i.e. column A is populated with the values 1,2 and 3 in random order; column B is populated with values a,b and c, also in random order. What would the formula be to determine the number of times that the combination of 1 and a appear side by side on the same row; over a worksheet with 10 rows. Thanks for any help in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
This seems to be headed in the right direction; the cell in which the
calculated amount is to be placed shows #value rather than actual number of occurances. Tex "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10=1),--(B1:B10="A")) Better to use cells to hold the criteria: C1 = 1 C2 = A =SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2)) Biff "Texicanslim" wrote in message ... I really need some help to write a formula with nested functions. How do I write a formula to calculate how many times a specific combination of values appear in two different columns. i.e. column A is populated with the values 1,2 and 3 in random order; column B is populated with values a,b and c, also in random order. What would the formula be to determine the number of times that the combination of 1 and a appear side by side on the same row; over a worksheet with 10 rows. Thanks for any help in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
Post the *EXACT* formula you tried.
The ranges have to be the same size and you can't use entire columns (A:A, B:B) as range references (unless you're using Excel 2007). Biff "Texicanslim" wrote in message ... This seems to be headed in the right direction; the cell in which the calculated amount is to be placed shows #value rather than actual number of occurances. Tex "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A10=1),--(B1:B10="A")) Better to use cells to hold the criteria: C1 = 1 C2 = A =SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2)) Biff "Texicanslim" wrote in message ... I really need some help to write a formula with nested functions. How do I write a formula to calculate how many times a specific combination of values appear in two different columns. i.e. column A is populated with the values 1,2 and 3 in random order; column B is populated with values a,b and c, also in random order. What would the formula be to determine the number of times that the combination of 1 and a appear side by side on the same row; over a worksheet with 10 rows. Thanks for any help in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
T. Valko wrote...
.... Better to use cells to hold the criteria: C1 = 1 C2 = A =SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2)) .... Could take advantage of the adjacency of the data ranges and enter the criteria in C1:D1, then try =SUMPRODUCT(--(A1:B10=C1:D1)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
That's the same as:
=COUNTIF(A1:A10,C1)+COUNTIF(B1:B10,D1) The OP wanted the count where both criteria are on the same row. Biff "Harlan Grove" wrote in message oups.com... T. Valko wrote... ... Better to use cells to hold the criteria: C1 = 1 C2 = A =SUMPRODUCT(--(A1:A10=C1),--(B1:B10=C2)) ... Could take advantage of the adjacency of the data ranges and enter the criteria in C1:D1, then try =SUMPRODUCT(--(A1:B10=C1:D1)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
T. Valko wrote...
That's the same as: =COUNTIF(A1:A10,C1)+COUNTIF(B1:B10,D1) The OP wanted the count where both criteria are on the same row. .... You're right. It'd need to be something like =COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2)) but nothing gained with just two sets of comparisons. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
Let me pick your brain......
=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2)) =SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2)) Other than one being a few keystrokes longer, which is better? Also, how do you get around the MMULT array size limit? I don't have a specific example in mind, just in general terms. Biff "Harlan Grove" wrote in message ups.com... T. Valko wrote... That's the same as: =COUNTIF(A1:A10,C1)+COUNTIF(B1:B10,D1) The OP wanted the count where both criteria are on the same row. ... You're right. It'd need to be something like =COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2)) but nothing gained with just two sets of comparisons. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
T. Valko wrote...
Let me pick your brain...... =COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2)) =SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2)) Other than one being a few keystrokes longer, which is better? For just two vector comparisons, neither is particularly good compared to your formula (modified) =SUMPRODUCT(--(A1:A10=C1),--(B1:B10=D1)) Things change when there are more. =COUNT(1/(MMULT(--(A3:G12=A1:G1),{1;1;1;1;1;1;1})=7)) vs =SUMPRODUCT(--(A3:A12=A1),--(B3:B12=B1),--(C3:C12=C1),--(D3:D12=D1),--(E3:E12=E1),--(F3:F12=F1), --(G3:G12=G1)) Also, how do you get around the MMULT array size limit? I don't have a specific example in mind, just in general terms. .... Not possible, but Excel 2003 seems to be able to handle a fair number of vectors with 5461 rows. Fill A1:J5500 with =ROW(). Enter the following. M1: 10 M2: 5461 N1: =SUMPRODUCT(MMULT(A1:INDEX($1:$65536,M2,M1),ROW(A1 :INDEX(A:A,M1))^0))/M1 N2: =(M2+1)*M2/2 N1 and N2 return the same result, the sum of all integers from 1 to 5461. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
=COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2))
=SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2)) Other than one being a few keystrokes longer, which is better? For just two vector comparisons, neither is particularly good compared to your formula (modified) For just 2 or 3 comparisons I wouldn't use MMULT. What I meant was, which is better as far as: =COUNT(1/(MMULT =SUMPRODUCT(--(MMULT It seems to me that Sumproduct has a slight advantage. Biff "Harlan Grove" wrote in message oups.com... T. Valko wrote... Let me pick your brain...... =COUNT(1/(MMULT(--(A1:B10=C1:D1),{1;1})=2)) =SUMPRODUCT(--(MMULT(--(A1:B10=C1:D1),{1;1})=2)) Other than one being a few keystrokes longer, which is better? For just two vector comparisons, neither is particularly good compared to your formula (modified) =SUMPRODUCT(--(A1:A10=C1),--(B1:B10=D1)) Things change when there are more. =COUNT(1/(MMULT(--(A3:G12=A1:G1),{1;1;1;1;1;1;1})=7)) vs =SUMPRODUCT(--(A3:A12=A1),--(B3:B12=B1),--(C3:C12=C1),--(D3:D12=D1),--(E3:E12=E1),--(F3:F12=F1), --(G3:G12=G1)) Also, how do you get around the MMULT array size limit? I don't have a specific example in mind, just in general terms. ... Not possible, but Excel 2003 seems to be able to handle a fair number of vectors with 5461 rows. Fill A1:J5500 with =ROW(). Enter the following. M1: 10 M2: 5461 N1: =SUMPRODUCT(MMULT(A1:INDEX($1:$65536,M2,M1),ROW(A1 :INDEX(A:A,M1))^0))/M1 N2: =(M2+1)*M2/2 N1 and N2 return the same result, the sum of all integers from 1 to 5461. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
T. Valko wrote...
.... For just 2 or 3 comparisons I wouldn't use MMULT. What I meant was, which is better as far as: =COUNT(1/(MMULT =SUMPRODUCT(--(MMULT It seems to me that Sumproduct has a slight advantage. .... Actually, I'd guess COUNT would be quicker because it's only checking each entry's data type while SUMPRODUCT is summing them, and I suspect summing always takes place in double precision. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating Nested Functions
I think you're right. Thanks, Harlan.
Biff "Harlan Grove" wrote in message ups.com... T. Valko wrote... ... For just 2 or 3 comparisons I wouldn't use MMULT. What I meant was, which is better as far as: =COUNT(1/(MMULT =SUMPRODUCT(--(MMULT It seems to me that Sumproduct has a slight advantage. ... Actually, I'd guess COUNT would be quicker because it's only checking each entry's data type while SUMPRODUCT is summing them, and I suspect summing always takes place in double precision. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
creating new functions | Excel Worksheet Functions | |||
creating a excel spread sheet using ffinancial functions | Excel Worksheet Functions | |||
Creating a nested COUNTIF???? | Excel Worksheet Functions | |||
Too many nested functions | Excel Worksheet Functions |