Find the Longest Run.
I have a column of random numbers that are conditionally formatted for the
font to be blue if the number is = 0 and red if <0. How do I find the longest run of numbers that are = than 0 and the longest run that are < 0 within the column. |
Find the Longest Run.
This UDF will find the longest run of <0 numbers in a column.
Function countmax(MyLetter As String, myRange _ As Range) As Integer Dim c As Range, TempMax As Integer, _ fReset As Boolean For Each c In myRange.Cells If c.Value < 0 Then TempMax = TempMax + 1 Else TempMax = 0 End If countmax = Application.WorksheetFunction _ .Max(countmax, TempMax) Next End Function Usage is........=countmax("<0",A1:A100) To count =0 just change the line "If c.Value < 0 Then" to "If c.Value = 0 Then" Change formula to =countmax("=0",A1:A100) Gord Dibben MS Excel MVP On Tue, 9 Oct 2007 11:44:01 -0700, dlbeiler wrote: I have a column of random numbers that are conditionally formatted for the font to be blue if the number is = 0 and red if <0. How do I find the longest run of numbers that are = than 0 and the longest run that are < 0 within the column. |
Find the Longest Run.
Assuming there are no empty cells in the range...
Both formulas are array formulas**. Longest run =0: =MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0,ROW(rn g)))) Longest run <0: =MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers that are conditionally formatted for the font to be blue if the number is = 0 and red if <0. How do I find the longest run of numbers that are = than 0 and the longest run that are < 0 within the column. |
Find the Longest Run.
Thank you for your assistance. The following is the column that I am trying
to solve: ..0005 -.0004 -.0002 ..0001 -.0001 ..0003 ..0001 ..0002 -.0003 ..0000 -.0003 ..0001 -.0005 -.0011 ..0007 ..0002 ..0001 -.0003 ..0001 ..0006 ..0008 ..0006 ..0007 -.0008 -.0016 ..0012 ..0006 -.0004 ..0000 -.0004 ..0007 -.0001 -.0002 ..0005 ..0028 -.0003 ..0003 -.0004 -.0004 -.0002 ..0003 ..0002 ..0004 ..0004 ..0002 -.0003 -.0003 -.0001 The value that I am looking for is a run of 5 for =0 and a run of 3 for <0. I inserted =MAX(FREQUENCY(IF(J310:J357=0,ROW(J310:J357)),IF( J310:J357<0,ROW(J310:J357)))) and received the following error message: "#value". What am I doing incorrectly? "T. Valko" wrote: Assuming there are no empty cells in the range... Both formulas are array formulas**. Longest run =0: =MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0,ROW(rn g)))) Longest run <0: =MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers that are conditionally formatted for the font to be blue if the number is = 0 and red if <0. How do I find the longest run of numbers that are = than 0 and the longest run that are < 0 within the column. |
Find the Longest Run.
received the following error message: "#value".
What am I doing incorrectly? Both formulas are array formulas**. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) You didn't enter the formulas as array formulas. Type the formula(s) but *don't* hit the ENTER key. Hold down both the CTRL key and the SHIFT key then hit ENTER. When done properly Excel will enclose the formula in squiggly braces { }. You can not just type these braces in. You *must* use the key combination. Also, any time you edit an array formula you must re-enter it as an array using the key combo. Based on your sample data I get: =0 = 5 <0 = 3 screencap: http://img225.imageshack.us/img225/2...gestrungr2.jpg -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... Thank you for your assistance. The following is the column that I am trying to solve: .0005 -.0004 -.0002 .0001 -.0001 .0003 .0001 .0002 -.0003 .0000 -.0003 .0001 -.0005 -.0011 .0007 .0002 .0001 -.0003 .0001 .0006 .0008 .0006 .0007 -.0008 -.0016 .0012 .0006 -.0004 .0000 -.0004 .0007 -.0001 -.0002 .0005 .0028 -.0003 .0003 -.0004 -.0004 -.0002 .0003 .0002 .0004 .0004 .0002 -.0003 -.0003 -.0001 The value that I am looking for is a run of 5 for =0 and a run of 3 for <0. I inserted =MAX(FREQUENCY(IF(J310:J357=0,ROW(J310:J357)),IF( J310:J357<0,ROW(J310:J357)))) and received the following error message: "#value". What am I doing incorrectly? "T. Valko" wrote: Assuming there are no empty cells in the range... Both formulas are array formulas**. Longest run =0: =MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0,ROW(rn g)))) Longest run <0: =MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers that are conditionally formatted for the font to be blue if the number is = 0 and red if <0. How do I find the longest run of numbers that are = than 0 and the longest run that are < 0 within the column. |
Find the Longest Run.
Which I got using the countmax UDF
Gord On Wed, 10 Oct 2007 17:39:49 -0400, "T. Valko" wrote: Based on your sample data I get: =0 = 5 <0 = 3 |
Find the Longest Run.
Thank you so much for your assistance. It now is working correctly.
"T. Valko" wrote: received the following error message: "#value". What am I doing incorrectly? Both formulas are array formulas**. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) You didn't enter the formulas as array formulas. Type the formula(s) but *don't* hit the ENTER key. Hold down both the CTRL key and the SHIFT key then hit ENTER. When done properly Excel will enclose the formula in squiggly braces { }. You can not just type these braces in. You *must* use the key combination. Also, any time you edit an array formula you must re-enter it as an array using the key combo. Based on your sample data I get: =0 = 5 <0 = 3 screencap: http://img225.imageshack.us/img225/2...gestrungr2.jpg -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... Thank you for your assistance. The following is the column that I am trying to solve: .0005 -.0004 -.0002 .0001 -.0001 .0003 .0001 .0002 -.0003 .0000 -.0003 .0001 -.0005 -.0011 .0007 .0002 .0001 -.0003 .0001 .0006 .0008 .0006 .0007 -.0008 -.0016 .0012 .0006 -.0004 .0000 -.0004 .0007 -.0001 -.0002 .0005 .0028 -.0003 .0003 -.0004 -.0004 -.0002 .0003 .0002 .0004 .0004 .0002 -.0003 -.0003 -.0001 The value that I am looking for is a run of 5 for =0 and a run of 3 for <0. I inserted =MAX(FREQUENCY(IF(J310:J357=0,ROW(J310:J357)),IF( J310:J357<0,ROW(J310:J357)))) and received the following error message: "#value". What am I doing incorrectly? "T. Valko" wrote: Assuming there are no empty cells in the range... Both formulas are array formulas**. Longest run =0: =MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0,ROW(rn g)))) Longest run <0: =MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers that are conditionally formatted for the font to be blue if the number is = 0 and red if <0. How do I find the longest run of numbers that are = than 0 and the longest run that are < 0 within the column. |
Find the Longest Run.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... Thank you so much for your assistance. It now is working correctly. "T. Valko" wrote: received the following error message: "#value". What am I doing incorrectly? Both formulas are array formulas**. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) You didn't enter the formulas as array formulas. Type the formula(s) but *don't* hit the ENTER key. Hold down both the CTRL key and the SHIFT key then hit ENTER. When done properly Excel will enclose the formula in squiggly braces { }. You can not just type these braces in. You *must* use the key combination. Also, any time you edit an array formula you must re-enter it as an array using the key combo. Based on your sample data I get: =0 = 5 <0 = 3 screencap: http://img225.imageshack.us/img225/2...gestrungr2.jpg -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... Thank you for your assistance. The following is the column that I am trying to solve: .0005 -.0004 -.0002 .0001 -.0001 .0003 .0001 .0002 -.0003 .0000 -.0003 .0001 -.0005 -.0011 .0007 .0002 .0001 -.0003 .0001 .0006 .0008 .0006 .0007 -.0008 -.0016 .0012 .0006 -.0004 .0000 -.0004 .0007 -.0001 -.0002 .0005 .0028 -.0003 .0003 -.0004 -.0004 -.0002 .0003 .0002 .0004 .0004 .0002 -.0003 -.0003 -.0001 The value that I am looking for is a run of 5 for =0 and a run of 3 for <0. I inserted =MAX(FREQUENCY(IF(J310:J357=0,ROW(J310:J357)),IF( J310:J357<0,ROW(J310:J357)))) and received the following error message: "#value". What am I doing incorrectly? "T. Valko" wrote: Assuming there are no empty cells in the range... Both formulas are array formulas**. Longest run =0: =MAX(FREQUENCY(IF(rng=0,ROW(rng)),IF(rng<0,ROW(rn g)))) Longest run <0: =MAX(FREQUENCY(IF(rng<0,ROW(rng)),IF(rng=0,ROW(rn g)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "dlbeiler" wrote in message ... I have a column of random numbers that are conditionally formatted for the font to be blue if the number is = 0 and red if <0. How do I find the longest run of numbers that are = than 0 and the longest run that are < 0 within the column. |
All times are GMT +1. The time now is 08:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com