Calculate median for a Range

M

msteven

Hello,

Please help!!

I have a worksheet with multiple result rows with varying rows within
each result.
The result row is also above the detail, not below.

What my code is doing is finding the ranges between the two result rows
and caculating the median using only the two ranges after the first
result row and before the last result row. I need it to perform the
median on the complete range, i.e. (rng1:rng2) and not (rng1, rng2).
Also, I need the result row above to be updated with the value, and not
the result row below. Code and example follows.

Many Thanks
Michelle


e.g.

Column E Column H
Result 20 5
Row 1 4
Row 2 6
Row 3 5
Row 4 5
Result 10 2
Row 1 7
Row 2 2
Row 3 1
Result 40 6
Row 1 2
Row 2 10
Row 3 20
Row 4 6
Row 5 2



Sub calc_median()

Dim rng1 As Range, rng2 As Range, total As Integer, i As Integer

total = 0#


While Cells(ActiveCell.Row, "B").Interior.ColorIndex <> xlNone
If Cells(ActiveCell.Row, "B").Value = "Result" Then
Cells(ActiveCell.Row, "H").Value = total
total = 0#
Set rng1 = Cells(ActiveCell.Row + 1, "E")
Cells(ActiveCell.Row + 1, "B").Select
End If
While Cells(ActiveCell.Row, "B").Value <> "Result"
Cells(ActiveCell.Row + 1, "B").Select
Wend
If Cells(ActiveCell.Row, "B").Value = "Result" Then
Set rng2 = Cells(ActiveCell.Row - 1, "E")
total = Application.Median(rng1, rng2)
total = total
End If
Wend
End Sub
 
Top