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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
On Feb 23, 11:48*am, "joeu2004" wrote:
"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. Hi Joe, This worked like a charm, =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))))) Now. I have data on "Sheet1" and want results on "Sheet2". How do I apply your formula so that it will look up every other column starting Column B on "Sheet1" and place the result on F6 "Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2"; Column F on "Sheet1" and place result on F8 on "Sheet2" and on ... until column is empty? Thanks again. Faye |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
"fzl2007" wrote:
=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))))) Now. I have data on "Sheet1" and want results on "Sheet2". How do I apply your formula so that it will look up every other column starting Column B on "Sheet1" and place the result on F6 "Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2"; Column F on "Sheet1" and place result on F8 on "Sheet2" and on ... until column is empty? I hope someone else can step up and help you with that. I can tell you that it is possible to do, and I could develop the formula. But there are trade-offs to consider and some testing. I just don't have that kind of time at the moment. I will tell you that a solution based on repeated instances of my formula above will become very time-consuming without boundaries. So you might consider posting some reasonable boundaries. For example, first and last possible row that might contain data, as well as last possible column in Sheet1. And you might consider whether a VBA macro solution would be acceptable. Even if the VBA implementation might be slower, at least it would only run when you decide to execute it. It could be implemented as "button" that you can click after downloading new data. One final caveat about my formula.... I just realized that I assume that the numbers start in row 2 (of all columns). And they are contiguous through the last row; no gaps in the data. Are those assumptions acceptable? Good luck with this. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif
On Feb 25, 1:00*am, "joeu2004" wrote:
"fzl2007" wrote: =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))))) Now. I have data on "Sheet1" and want results on "Sheet2". How do I apply your formula so that it will look up every other column starting Column B on "Sheet1" and place the result on F6 "Sheet2", Column D on "Sheet1" and place result on F7 "Sheet2"; Column F on "Sheet1" and place result on F8 on "Sheet2" and on ... until column is empty? I hope someone else can step up and help you with that. I can tell you that it is possible to do, and I could develop the formula.. But there are trade-offs to consider and some testing. *I just don't have that kind of time at the moment. I will tell you that a solution based on repeated instances of my formula above will become very time-consuming without boundaries. So you might consider posting some reasonable boundaries. *For example, first and last possible row that might contain data, as well as last possible column in Sheet1. And you might consider whether a VBA macro solution would be acceptable. Even if the VBA implementation might be slower, at least it would only run when you decide to execute it. *It could be implemented as "button" that you can click after downloading new data. One final caveat about my formula.... *I just realized that I assume that the numbers start in row 2 (of all columns). *And they are contiguous through the last row; no gaps in the data. Are those assumptions acceptable? Good luck with this. Yes, your assumptions are correct with one thing only, the column might be of the same sign and the result of the count for the rows would be desired. Now it would show #NA... Thank you for your time. I greatly appreciate it. |
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 |