Excel - selecting varied length ranges for calculation

J

Julie King

:rolleyes: I have two Excel columns (over 6000 rows) - one ha
numerical values and the other has either 0's or 1's. What I need is
way to select a range of cells in the first column (col A) depending o
when the second column (col B) switches from value 0 to 1. So let'
say that the first row of col B contains a 1 (which it always will)
and the next 6 rows are all 0's, and the 8th row is a 1. Then th
range I need to select in col A is from A1 thru A7. Next, I need t
calculate the median of A1 thru A7 and paste that in a third column.
My next range selected will then begin with A8 and end with the cel
prior to the next one valued with a 1. I need to repeat this for th
entire 2 columns. The values in col B can change from a zero to a 1 a
any time. Help!
Thanks in advance
 
A

AlfD

Hi!

I don't know if you've got there yet, but here is a sub which seems t
do the business.

Numbers are in col A: indicators (1 or 0) are in col B and resultan
medians are in col D.

I have assumed that data starts at row 2 - if it is different, jus
change the two 2's

Sub findmeds()
counter = 2
For i = 2 To 6000
If Range("B" & i).Value = 1 Then
Range("D" & counter)
Application.WorksheetFunction.Median(Range("A" & counter, "A" & i
1))
counter = i
End If
Next i
End Sub

Al
 
J

Julie King

:) Thank you so very much Alf! I will give it a try... I sincerel
appreciate your help
 
Top