subtotal using the median functoin

S

susan lasalle

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.
 
A

Aladin Akyurek

=MEDIAN(IF(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)),Range))

which must be confirmed with control+shift+enter instead of just with enter.

Note that Range is the unfiltered, full range of interest.
 
S

susan lasalle

there 24hrs worth of data taken every second or so. we are trying to find
the median of one of the measurements during each half hour increment. we
need median instead of average because of some occasional weird spikes in
the data which throw the average off...

I may be going about this wrong. what I did was make an elapsed time
column, then use a look up table in a column as a "marker" - everything in
the first 1/2 hour got a '1', the second half hour got a '2'...etc. then I
subtotaled in the "marker" column using data-subtotal ...I just would like
to have the median function somehow, but using data-subtotal I can only use
average...thanks again.

time amt elapsed marker
12:00 2.1 0 1
12:02 2.0 :02 1
12:03 7.4 :03 1
12:04 0.2 :04 1
..
..
..

12:30 2.2 :30 1
12:31 2.1 :31 2
 
P

Peter Atherton

-----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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top