Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
I want to count the rows if the value of the column, from bottom up,
changes sign (from positive to negative or vise versa.). As soon as the sign is changed or the number is the value of a zero, counter finishes counter. For example, -2.1 31.2 -2.1 0.3 44.8 1.6 -1.1 -9.4 5.3 44 10.4 9 13.1 -2.2 0 12.2 -11.1 3.2 counter = 3 counter = 2 counter = 1 Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
"fzl2007" wrote:
I want to count the rows if the value of the column, from bottom up, changes sign [....] For example, -2.1 31.2 -2.1 0.3 44.8 1.6 -1.1 -9.4 5.3 44 10.4 9 13.1 -2.2 0 12.2 -11.1 3.2 counter=3 counter=2 counter=1 For the data in A1:A6, enter the following __array_formula__ (press ctrl+shift+Enter instead of Enter): =6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))) 6 can be replaced by COUNT(A1:A6). MATCH returns the last row number relative to A1 whose sign differs from A6. 6-MATCH(...) makes that row number relative to A6. Caveat: This returns a #N/A if there is no sign change. If you do not like that, you can do the following in XL2007 and later: =IFERROR(6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))),"") In XL2003 and earlier: =IF(SUMPRODUCT(--(SIGN(A6)<SIGN(A1:A5)))0, 6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))),"") Both alternatives are also __array_formulas__ (press ctrl+shift+Enter instead of Enter). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
On Feb 21, 2:52*pm, "joeu2004" wrote:
"fzl2007" wrote: I want to count the rows if the value of the column, from bottom up, changes sign [....] For example, -2.1 * * * * 31.2 * * * * -2.1 0.3 * * * * *44.8 * * * * 1.6 -1.1 * * * * -9.4 * * * * 5.3 44 * * * * * 10.4 * * * * 9 13.1 * * * * -2.2 * * * * 0 12.2 * * * * -11.1 * * * *3.2 counter=3 * *counter=2 * *counter=1 For the data in A1:A6, enter the following __array_formula__ (press ctrl+shift+Enter instead of Enter): =6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))) 6 can be replaced by COUNT(A1:A6). MATCH returns the last row number relative to A1 whose sign differs from A6. 6-MATCH(...) makes that row number relative to A6. Caveat: *This returns a #N/A if there is no sign change. *If you do not like that, you can do the following in XL2007 and later: =IFERROR(6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))),"") In XL2003 and earlier: =IF(SUMPRODUCT(--(SIGN(A6)<SIGN(A1:A5)))0, *6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))),"") Both alternatives are also __array_formulas__ (press ctrl+shift+Enter instead of Enter). I just ran the array formular on this list, =6-MATCH(2,1/ (SIGN(A6)<SIGN(A1:A5))), the solution should be 2 but it gave a 1... Also, how do I change the function so that it will catch the column rows as it changes? The column data (range) is dynamic. Thanks. 6.4 11.2 2.5 7.3 6.1 -5.5 0.2 5.8 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
"fzl2007" wrote:
On Feb 21, 2:52 pm, "joeu2004" wrote: For the data in A1:A6, enter the following __array_formula__ (press ctrl+shift+Enter instead of Enter): =6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))) 6 can be replaced by COUNT(A1:A6). [....] I just ran the array formular on this list, =6-MATCH(2,1/ (SIGN(A6)<SIGN(A1:A5))), the solution should be 2 but it gave a 1 6.4 11.2 2.5 7.3 6.1 -5.5 0.2 5.8 There operative words were "for the data in A1:A6". Since your new example is 8 cells, presumably in A1:A8, the formula needs to be adapted accordingly, to wit: =8-MATCH(2,1/(SIGN(A8)<SIGN(A1:A7))) "fzl2007" wrote: Also, how do I change the function so that it will catch the column rows as it changes? The column data (range) is dynamic. The simplest way to do that is to ensure that there is always a cell above and below (empty or containing text), and you use Insert and Delete to add and remove data. Then the dynamic formula can be: =COUNT($A$1:$A$10) -MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10))))) That assumes that A1 and A10 are the rows above and below 8 cells of data. Caveat: OFFSET is a volatile function. So that formula will be recalculated every time any cell in any worksheet in the workbook is edited. If you have a lot of such formulas, that can degrade performance significantly. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
On Feb 22, 1:21*pm, "joeu2004" wrote:
"fzl2007" wrote: On Feb 21, 2:52 pm, "joeu2004" wrote: For the data in A1:A6, enter the following __array_formula__ (press ctrl+shift+Enter instead of Enter): =6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))) 6 can be replaced by COUNT(A1:A6). [....] I just ran the array formular on this list, *=6-MATCH(2,1/ (SIGN(A6)<SIGN(A1:A5))), the solution should be 2 but it gave a 1 6.4 11.2 2.5 7.3 6.1 -5.5 0.2 5.8 There operative words were "for the data in A1:A6". *Since your new example is 8 cells, presumably in A1:A8, the formula needs to be adapted accordingly, to wit: =8-MATCH(2,1/(SIGN(A8)<SIGN(A1:A7))) "fzl2007" wrote: Also, how do I change the function so that it will catch the column rows as it changes? The column data (range) is dynamic. The simplest way to do that is to ensure that there is always a cell above and below (empty or containing text), and you use Insert and Delete to add and remove data. *Then the dynamic formula can be: =COUNT($A$1:$A$10) -MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10*))))) That assumes that A1 and A10 are the rows above and below 8 cells of data.. Caveat: *OFFSET is a volatile function. *So that formula will be recalculated every time any cell in any worksheet in the workbook is edited. If you have a lot of such formulas, that can degrade performance significantly.- Hide quoted text - - Show quoted text - The column data will be added and the fomula needs to capture the new data added. There are many such columns. This will not be a good solution as we will need to update the formula every time when data changes. I wonder if there is another solution... that will capture the range with data for the column... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
On Feb 22, 1:48*pm, fzl2007 wrote:
On Feb 22, 1:21*pm, "joeu2004" wrote: "fzl2007" wrote: On Feb 21, 2:52 pm, "joeu2004" wrote: For the data in A1:A6, enter the following __array_formula__ (press ctrl+shift+Enter instead of Enter): =6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5))) 6 can be replaced by COUNT(A1:A6). [....] I just ran the array formular on this list, *=6-MATCH(2,1/ (SIGN(A6)<SIGN(A1:A5))), the solution should be 2 but it gave a 1 6.4 11.2 2.5 7.3 6.1 -5.5 0.2 5.8 There operative words were "for the data in A1:A6". *Since your new example is 8 cells, presumably in A1:A8, the formula needs to be adapted accordingly, to wit: =8-MATCH(2,1/(SIGN(A8)<SIGN(A1:A7))) "fzl2007" wrote: Also, how do I change the function so that it will catch the column rows as it changes? The column data (range) is dynamic. The simplest way to do that is to ensure that there is always a cell above and below (empty or containing text), and you use Insert and Delete to add and remove data. *Then the dynamic formula can be: =COUNT($A$1:$A$10) -MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10 *))))) That assumes that A1 and A10 are the rows above and below 8 cells of data. Caveat: *OFFSET is a volatile function. *So that formula will be recalculated every time any cell in any worksheet in the workbook is edited. If you have a lot of such formulas, that can degrade performance significantly.- Hide quoted text - - Show quoted text - The column data will be added and the fomula needs to capture the new data added. There are many such columns. This will not be a good solution as we will need to update the formula every time when data changes. I wonder if there is another solution... that will capture the range with data for the column... Will a macro do? Sub MarkChangeSAS() Dim r As Long Dim c As Long Rows(10).ClearContents For c = 1 To Cells(1, Columns.Count).End(xlToLeft).Column For r = Cells(Rows.Count, c).End(xlUp).Row To 2 Step -1 If Sgn(Cells(r, c)) < Sgn(Cells(r - 1, c)) Or Cells(r, c) = 0 Then Cells(10, c) = Cells(Rows.Count, c).End(xlUp).Row - r + 1 Exit For End If Next r Next c End Sub |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
"fzl2007" wrote:
=COUNT($A$1:$A$10) -MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10*))))) [....] The column data will be added and the fomula needs to capture the new data added. There are many such columns. This will not be a good solution as we will need to update the formula every time when data changes. That depends on how you are capturing the data. Again, if you can __insert__ data between A1 and A10, the formula will adjust automagically. More generally, assuming that column B contains only your data starting in row 2 (specially, no numeric data after; text before and after is okay), you could use the following __array_formula__ (press ctrl+shift+Enter instead of just Enter): =COUNT(B2:INDEX(B:B,MATCH(1E+300,B:B))) - MATCH(2,1/(SIGN(INDEX(B:B,MATCH(1E+300,B:B))) <SIGN(B2:INDEX(B:B,MATCH(1E+300,B:B))))) Note: Given the assumptions, you could write COUNT(B:B) instead. I wanted to show the more general form just in case some of the assumptions do not apply. That is an extremely inefficient formula, particularly in XL2007 and later with a limit 1M rows (1M = 1,048,576). If you know your data will not extend to more than row 10,000, it would be much better to replace B:B with B1:B10000. (Note: B1, not B2.) It would be even better if you put =MATCH(1E300,B1:B10000) into a helper cell (X1), and replace the repeated MATCH expressions with X1 in the formula. And if all of the "many such columns" are the same length, it would be better if you also put =COUNT(B2:INDEX(B:B,X1)) into a helper cell (X2). Thus, you formula becomes: =X2-MATCH(2,1/(SIGN(INDEX(B:B,X1))<SIGN(B2:INDEX(B:B,X1)))) The good news is: INDEX is not a volatile function. The formula is recalculated only when column B changes. However, it appears that Excel does recalculate formulas with INDEX when the file is opened. So you might experience some delay at the outset; and you will be prompted to save or not when you close the file, even if you did not make any changes yourself. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |