| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count between Hi and Low
How do I count the rows between Hi and Low stock values?
And Low to Hi. Col B C 58.0 Low 59.7 63.0 Hi 58.5 60.4 62.2 Low 65.4 63.0 64.3 66.4 Hi 70.0 Hi 66.4 64.0 62.0 Low Put result in Col D With Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count between Hi and Low
One way that I sole this general problem is in the next available column to
the right I create an index with triggers.. i.e. the following, D2 reffering the cell in column D next to say, "Low": D2 = if(C2="Low",1,if(C2="High",-1,0)) D3 = if(C3="Low",1,if(C2="High",-1,0)) E2 = D2 E3 = E2 + D3 F2 = if(E2=1,1,0) G2 = if(E2=-1,1,0) Then sum up either F2:F20 or G2:G20 at the end to get the number of rows between the low to high or high to low. You can also add triggers so if you only want the first interval or second interval etc. Jonathan "smandula" wrote: How do I count the rows between Hi and Low stock values? And Low to Hi. Col B C 58.0 Low 59.7 63.0 Hi 58.5 60.4 62.2 Low 65.4 63.0 64.3 66.4 Hi 70.0 Hi 66.4 64.0 62.0 Low Put result in Col D With Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count between Hi and Low
Hi,
You can try this: Sub HiLowCnt() Dim cell As Range, rng As Range Dim prev Dim hicnt As Integer, locnt As Integer Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng If cell.Offset(0, 1) = "Low" Then locnt = locnt + 1 prev = "Low" ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then locnt = locnt + 1 End If If cell.Offset(0, 1) = "Hi" Then hicnt = hicnt + 1 prev = "Hi" ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then hicnt = hicnt + 1 End If Next cell Range("D1").Value = "Low Cnt " & locnt Range("D2").Value = "Hi Cnt " & hicnt End Sub Charles "smandula" wrote in message ... How do I count the rows between Hi and Low stock values? And Low to Hi. Col B C 58.0 Low 59.7 63.0 Hi 58.5 60.4 62.2 Low 65.4 63.0 64.3 66.4 Hi 70.0 Hi 66.4 64.0 62.0 Low Put result in Col D With Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count between Hi and Low
Thanks very much for your reply. The VBA works perfect on total.
Sorry to say I need the number or count between grouping. For instance Low+blank=2 next item Hi+blank+blank=3 next item Low+blank+blank+blank=4 next item Hi =1 next item Hi+blank+lank=3 Could this grouping be possible? Thank Again "Charles Harmon" wrote in message ... Hi, You can try this: Sub HiLowCnt() Dim cell As Range, rng As Range Dim prev Dim hicnt As Integer, locnt As Integer Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng If cell.Offset(0, 1) = "Low" Then locnt = locnt + 1 prev = "Low" ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then locnt = locnt + 1 End If If cell.Offset(0, 1) = "Hi" Then hicnt = hicnt + 1 prev = "Hi" ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then hicnt = hicnt + 1 End If Next cell Range("D1").Value = "Low Cnt " & locnt Range("D2").Value = "Hi Cnt " & hicnt End Sub Charles "smandula" wrote in message ... How do I count the rows between Hi and Low stock values? And Low to Hi. Col B C 58.0 Low 59.7 63.0 Hi 58.5 60.4 62.2 Low 65.4 63.0 64.3 66.4 Hi 70.0 Hi 66.4 64.0 62.0 Low Put result in Col D With Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count between Hi and Low
How about a variation on Jonathan's solution.
In 'D' D2 = if (C2<"",D1+1,1) This looks at column 'C' and 'counts' till it finds another entry in 'C' In 'E' E2 if (C3 < "" ,D2,"") This looks at column C and displays the 'count' just before there is an entry in 'C' This any use? Chris "smandula" wrote in message ... Thanks very much for your reply. The VBA works perfect on total. Sorry to say I need the number or count between grouping. For instance Low+blank=2 next item Hi+blank+blank=3 next item Low+blank+blank+blank=4 next item Hi =1 next item Hi+blank+lank=3 Could this grouping be possible? Thank Again "Charles Harmon" wrote in message ... Hi, You can try this: Sub HiLowCnt() Dim cell As Range, rng As Range Dim prev Dim hicnt As Integer, locnt As Integer Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng If cell.Offset(0, 1) = "Low" Then locnt = locnt + 1 prev = "Low" ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then locnt = locnt + 1 End If If cell.Offset(0, 1) = "Hi" Then hicnt = hicnt + 1 prev = "Hi" ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then hicnt = hicnt + 1 End If Next cell Range("D1").Value = "Low Cnt " & locnt Range("D2").Value = "Hi Cnt " & hicnt End Sub Charles "smandula" wrote in message ... How do I count the rows between Hi and Low stock values? And Low to Hi. Col B C 58.0 Low 59.7 63.0 Hi 58.5 60.4 62.2 Low 65.4 63.0 64.3 66.4 Hi 70.0 Hi 66.4 64.0 62.0 Low Put result in Col D With Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count between Hi and Low
Hi,
Lets try this: Sub HiLowCnt() Dim cell As Range, rng As Range Dim prev Dim hicnt As Integer, locnt As Integer Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) prev = "Low" For Each cell In rng If cell.Offset(0, 1) = "Low" Then If prev = "Hi" Then cell.Offset(-hicnt, 3) = "Hi Cnt " & hicnt hicnt = 0 End If locnt = locnt + 1 prev = "Low" ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then locnt = locnt + 1 ElseIf cell.Offset(0, 1) = "Hi" Then If prev = "Low" Then cell.Offset(-locnt, 3) = "Low Cnt " & locnt locnt = 0 End If hicnt = hicnt + 1 prev = "Hi" ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then hicnt = hicnt + 1 End If If prev = "Hi" And cell.Offset(1, 0) = "" Then cell.Offset(, 3) = "Hi Cnt " & hicnt hicnt = 0 End If If prev = "Low" And cell.Offset(1, 0) = "" Then cell.Offset(, 3) = "Low Cnt " & locnt locnt = 0 End If Next cell End Sub There are other and better ways, but ...... Charles "smandula" wrote in message ... Thanks very much for your reply. The VBA works perfect on total. Sorry to say I need the number or count between grouping. For instance Low+blank=2 next item Hi+blank+blank=3 next item Low+blank+blank+blank=4 next item Hi =1 next item Hi+blank+lank=3 Could this grouping be possible? Thank Again "Charles Harmon" wrote in message ... Hi, You can try this: Sub HiLowCnt() Dim cell As Range, rng As Range Dim prev Dim hicnt As Integer, locnt As Integer Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng If cell.Offset(0, 1) = "Low" Then locnt = locnt + 1 prev = "Low" ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then locnt = locnt + 1 End If If cell.Offset(0, 1) = "Hi" Then hicnt = hicnt + 1 prev = "Hi" ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then hicnt = hicnt + 1 End If Next cell Range("D1").Value = "Low Cnt " & locnt Range("D2").Value = "Hi Cnt " & hicnt End Sub Charles "smandula" wrote in message ... How do I count the rows between Hi and Low stock values? And Low to Hi. Col B C 58.0 Low 59.7 63.0 Hi 58.5 60.4 62.2 Low 65.4 63.0 64.3 66.4 Hi 70.0 Hi 66.4 64.0 62.0 Low Put result in Col D With Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Count between Hi and Low
That's exactly it!!
Many Thanks The Macro is elegant. With Thanks Steve Lets try this: Sub HiLowCnt() Dim cell As Range, rng As Range Dim prev Dim hicnt As Integer, locnt As Integer Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) prev = "Low" For Each cell In rng If cell.Offset(0, 1) = "Low" Then If prev = "Hi" Then cell.Offset(-hicnt, 3) = "Hi Cnt " & hicnt hicnt = 0 End If locnt = locnt + 1 prev = "Low" ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then locnt = locnt + 1 ElseIf cell.Offset(0, 1) = "Hi" Then If prev = "Low" Then cell.Offset(-locnt, 3) = "Low Cnt " & locnt locnt = 0 End If hicnt = hicnt + 1 prev = "Hi" ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then hicnt = hicnt + 1 End If If prev = "Hi" And cell.Offset(1, 0) = "" Then cell.Offset(, 3) = "Hi Cnt " & hicnt hicnt = 0 End If If prev = "Low" And cell.Offset(1, 0) = "" Then cell.Offset(, 3) = "Low Cnt " & locnt locnt = 0 End If Next cell End Sub There are other and better ways, but ...... Charles "smandula" wrote in message ... Thanks very much for your reply. The VBA works perfect on total. Sorry to say I need the number or count between grouping. For instance Low+blank=2 next item Hi+blank+blank=3 next item Low+blank+blank+blank=4 next item Hi =1 next item Hi+blank+lank=3 Could this grouping be possible? Thank Again "Charles Harmon" wrote in message ... Hi, You can try this: Sub HiLowCnt() Dim cell As Range, rng As Range Dim prev Dim hicnt As Integer, locnt As Integer Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng If cell.Offset(0, 1) = "Low" Then locnt = locnt + 1 prev = "Low" ElseIf cell.Offset(0, 1) = "" And prev = "Low" Then locnt = locnt + 1 End If If cell.Offset(0, 1) = "Hi" Then hicnt = hicnt + 1 prev = "Hi" ElseIf cell.Offset(0, 1) = "" And prev = "Hi" Then hicnt = hicnt + 1 End If Next cell Range("D1").Value = "Low Cnt " & locnt Range("D2").Value = "Hi Cnt " & hicnt End Sub Charles "smandula" wrote in message ... How do I count the rows between Hi and Low stock values? And Low to Hi. Col B C 58.0 Low 59.7 63.0 Hi 58.5 60.4 62.2 Low 65.4 63.0 64.3 66.4 Hi 70.0 Hi 66.4 64.0 62.0 Low Put result in Col D With Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
| Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
