-----Original Message-----
I have a lot of data that I need to subtotal, but I need to use the median
function rather than any of the functions available with the subtotal
command. any ideas??? thanks for your help.
Susan
This is A vb solution. The test data has been sorted and
given Excel Subtotals. The VB solution is based on these
subtotal format i.e.
Index Job Part no Price
1 687196004-9 19-002 2,335.30
19-002 Total 2,335.30
7 687223007-5 19-038 257.82
13 687223007-5 19-038 131.07
19-038 Total 388.89
The Subtotals are then converted to Median values i.e
Index Job Part no Price
1 687196004-9 19-002 2,335.30
19-002 Median 2,335.30
7 687223007-5 19-038 257.82
13 687223007-5 19-038 131.07
19-038 Median 194.445
Sub MyMedian()
Dim nr As Long
Dim c As Variant
Dim rng As Range, temp As Range, r As Long, r2 As Long
Dim med As Double
nr = Application.WorksheetFunction.CountA(Range("C:C"))
Set rng = Range(Cells(2, 2), Cells(nr, 2))
On Error Resume Next
'rng is in column 2
For Each c In rng
r2 = c.Row
If c = c.Offset(-1, 0) Then
r = r + 1
ElseIf IsEmpty(c) Then
r2 = r2 - 1
Set temp = Range(Cells(r2 - r, 4), Cells(r2, 4))
med = Application.WorksheetFunction.Median(temp)
c.Offset(0, 2) = med
v = c.Offset(0, 1)
v = Left(v, Len(v) - 5) & "Median"
c.Offset(0, 1) = v
r = 0
End If
Next c
End Sub
Unfortunately, youo can not then just delete the subtotals
as the formulas are overwritten. But you can increase the
offset values by 2 (the second value) so
c.offset(0,3)=v and
c.offset(0,4) = med
This will allow you to remove subtotals in the normal way.
Sorry for the delay
Regards
Peter
Regards
Peter