|
Counting a series of text and/or numbers within columns
I have the following table:
A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Try these array formulas** :
Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Thanks! You're a genius. :-)
For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
For the consecutive 0s, i'd like to know
if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
If the cells stops at the end of the consecutive 0s, then the formula cell
will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Anyone? Please don't give up...i'm sure someone out there can help me figure
this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Building on Biff's formula above (**CSE):
=LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
What if there's multiple instances of 3 consecutive 0s? I assume that's
possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Hi, sorry but the formula doesn't seem to work as it returns #N/A result.
Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Are you saying there there will only be 1 instance of 3 consecutive 0s (if
at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
No Biff. The columns will have their rows updated weekly hence it will grow.
So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Ok, then as I suspected this can't be done with a single formula.
Let me see what I can come up with. It may take a while! -- Biff Microsoft Excel MVP "andrew" wrote in message ... No Biff. The columns will have their rows updated weekly hence it will grow. So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Thanks Biff! Better late than never.. :-)
"T. Valko" wrote: Ok, then as I suspected this can't be done with a single formula. Let me see what I can come up with. It may take a while! -- Biff Microsoft Excel MVP "andrew" wrote in message ... No Biff. The columns will have their rows updated weekly hence it will grow. So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence |
Counting a series of text and/or numbers within columns
Maybe this (CSE**):
=SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9 <0,ROW(A1:A9),0))=3) *(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C90))) assuming you only want to count instances where column c is negative. The data range can be extended below the last populated cell so that the formula updates when additional data is added. "andrew" wrote: No Biff. The columns will have their rows updated weekly hence it will grow. So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Thanks Lori, you're a legend! It worked to perfection for both positive and
negative! Hey Biff, thanks for your help too! By the way, can i use the same formula to just count the number of times the occurences happen? I.e. (with the same table) 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row if cell C5 is negative digit (-0.5 in this case), then if match counts it as 1. If not, formula cell remains as 0. If the occurence of 3 consecutive 0s happens 8 times within the same column then the formula cell will also capture the total occurence as 8. Possible right? "Lori" wrote: Maybe this (CSE**): =SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9 <0,ROW(A1:A9),0))=3) *(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C90))) assuming you only want to count instances where column c is negative. The data range can be extended below the last populated cell so that the formula updates when additional data is added. "andrew" wrote: No Biff. The columns will have their rows updated weekly hence it will grow. So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Hmmm....
If the OP says that works then I misunderstood what they wanted. I thought they just wanted to calculate this portion (for the individual instances): (C1:C9<0)*(D1:D9-E1:E9+C1:C90) Nice formula, BTW! -- Biff Microsoft Excel MVP "Lori" wrote in message ... Maybe this (CSE**): =SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9 <0,ROW(A1:A9),0))=3) *(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C90))) assuming you only want to count instances where column c is negative. The data range can be extended below the last populated cell so that the formula updates when additional data is added. "andrew" wrote: No Biff. The columns will have their rows updated weekly hence it will grow. So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in a sequence of 3 times within a column? Appreciate any help, thanks! |
Counting a series of text and/or numbers within columns
Additional question.
Can i use the same formula to count just the total the number of times the occurences happen? (i.e. with the same table) 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row if cell C5 is negative digit (-0.5 in this case), then if match counts it as 1. If not, formula cell remains as 0. "andrew" wrote: Thanks Lori, you're a legend! It worked to perfection for both positive and negative! Hey Biff, thanks for your help too! By the way, can i use the same formula to just count the number of times the occurences happen? I.e. (with the same table) 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row if cell C5 is negative digit (-0.5 in this case), then if match counts it as 1. If not, formula cell remains as 0. If the occurence of 3 consecutive 0s happens 8 times within the same column then the formula cell will also capture the total occurence as 8. Possible right? "Lori" wrote: Maybe this (CSE**): =SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9 <0,ROW(A1:A9),0))=3) *(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C90))) assuming you only want to count instances where column c is negative. The data range can be extended below the last populated cell so that the formula updates when additional data is added. "andrew" wrote: No Biff. The columns will have their rows updated weekly hence it will grow. So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "andrew" wrote in message ... I have the following table: A B 0 W 1 W 0 D 0 W 0 D 1 W 0 W 1 D Is there a way to count or sum up a series of text or number within a column? E.g. i) count the number of times the alphabet W appears in sequence of 2 times (i.e. B2 and B3 in a column)? Table above shows W appearing twice in sequence. ii) count the number of times the number zero (0) appears in |
Counting a series of text and/or numbers within columns
Unfortunately after testing with my actual data (consisting of 40 rows over
20 worksheets), the formula provided (i was using Lori's) started to give me the following error message (strangely not all but different cells in different w'sheet where the formula resides): Value Not Available error (i.e. #N/A) i followed exactly what was provided, and did a variation to also calculate instances where column c is positive. Let me paste the formula used he Negative instances: =SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9 <0,ROW(A1:A9),0))=3)*(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C90))) Positive instances: =SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9 <0,ROW(A1:A9),0))=3)*(1-FREQUENCY(2,--A1:A9)),(C1:C90)*(D1:D9-E1:E9+C1:C90))) Weird thing is, sometimes it just adds 1 instance when there is 2, while other times it gives me the #N/A error. Need your input here guys, we're so so close..... "T. Valko" wrote: Hmmm.... If the OP says that works then I misunderstood what they wanted. I thought they just wanted to calculate this portion (for the individual instances): (C1:C9<0)*(D1:D9-E1:E9+C1:C90) Nice formula, BTW! -- Biff Microsoft Excel MVP "Lori" wrote in message ... Maybe this (CSE**): =SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9 <0,ROW(A1:A9),0))=3) *(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C90))) assuming you only want to count instances where column c is negative. The data range can be extended below the last populated cell so that the formula updates when additional data is added. "andrew" wrote: No Biff. The columns will have their rows updated weekly hence it will grow. So if there are more than one instance of the same sequence 0s, the formula will count them. "T. Valko" wrote: Are you saying there there will only be 1 instance of 3 consecutive 0s (if at all)? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Hi, sorry but the formula doesn't seem to work as it returns #N/A result. Based on the sample table, it should return 1 as the result was positive [i.e. (1-0)-0.5 = +0.5]. I'll try to explain clearer using the same table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table (note: A-E does not count as row), the formula cell will check for the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit (-0.5 in this case), then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") = +0.5; 4) if total is + (positive) then it registers in formula cell as 1 - which, in the above table, applies with the result in 1. 5) IF total is -(negative) then it registers in formula cell as 0. Example is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5. Please help me as i'm a novice with Excel and its driving me nuts... "T. Valko" wrote: What if there's multiple instances of 3 consecutive 0s? I assume that's possible since their original request was to be able to count the number of instances that meet a condition. That's how I interpret this so a single formula couldn't be used if that's the case. Think we need some clarification from the OP. -- Biff Microsoft Excel MVP "Lori" wrote in message ... Building on Biff's formula above (**CSE): =LOOKUP(2,(D2:D9-E2:E9+C2:C90)/(C2:C9<0)/ (FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2: A9),0))=3)) Notes: - This returns 1,0 or #N/A depending on which conditions are met. - If there is more than one match the result of the last match is returned. - A sequence ending in three 0's is not counted and will return #n/a if no other match is found. "andrew" wrote: Anyone? Please don't give up...i'm sure someone out there can help me figure this out...thanks a million in advance. "andrew" wrote: If the cells stops at the end of the consecutive 0s, then the formula cell will return a 0 or nil. Is this possible? Lets try using a simpler table: A B C D E 1 W -0.5 2 2 0 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 1 D -0.5 1 0 Using the above table, an independent formula cell will check the following conditions: 1) three (3) consecutive 0s are met in column A, then if match; 2) checks the following row (row 5 for above) if cell C5 is negative digit, then if match; 3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5; 4) if total is + (positive) then it registers in formula cell as 1. If total is - (negative) it registers as 0 (zero). Can SUMPRODUCT with different array formulas be used for the above? "T. Valko" wrote: For the consecutive 0s, i'd like to know if a formula is possible to... Good grief!!!! My head is spinning on that one! I have no idea how to do that in a single. It could be done using multiple helper formulas but there's a problem with your requirement. taking into account the cell of column C immediately after the consecutive 0s What if the cells are like this: A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 There is no cell immediately after the consecutive 0s. ??? -- Biff Microsoft Excel MVP "andrew" wrote in message ... Thanks! You're a genius. :-) For the consecutive 0s, i'd like to know if a formula is possible to calculate the following (see table below): A B C D E 0 W -0.5 2 2 1 W +0.5 2 2 0 D +0.5 2 1 0 W -0.5 1 2 0 D -0.5 1 2 1 W -0.5 2 1 0 W +0.5 1 1 1 D +0.5 1 2 When the condition of consecutive 0s (in this table instance is 3 in a row) has been met, the cell has another condition to calculate the following: i) taking into account the cell of column C immediately after the consecutive 0s (i.e. C7 in sample table above), it needs to take (D7-E7)+C7 to equate with either a positive or negative result. Based on above table, (D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result) ii) another example would be if the roles are reversed for D7 and E7 where D7 is 1 while E7 is 2. The result would be: (D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result) With the above 2 examples, the formula cell will then register a count (1) if its a positive result, and zero/none (0) if its a negative result. Is this possible? Thanks again! "T. Valko" wrote: Try these array formulas** : Count 2 consecutive Ws: =SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<"w", ROW(B2:B9)))=2)) Count 3 consecutive 0s. Assumes no empty cells within the range. Empty cells evaluate as 0 and could cause incorrect results. If there might be empty cells the formula can be tweaked to account for them. =SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<0,ROW( A2:A9)))=3)) Note that these formulas are explicit in how they count. W W W W That would not be counted as 2 instances of 2 consecutive Ws. That is considered 1 instance of 4 consecutive Ws. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) |
| All times are GMT +1. The time now is 08:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com