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

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

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

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

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

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

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