median calculation after auto filter

[hinchah]

i have 100 rows of data. when i use the autofilter my data changes to 15 rows. how can i write the median formula in cell B16 to find the median of the 15 rows of filtered data?? i am currently using this formula to find the average, =subtotal(1,B1:B100), but i cant figure out a way to do it for the median.

thanks

 

[hinchah]

i made a mistake. i want to write the formula in cell B101, after the data is filtered to find the median.

thanks

 

[Yogi Anand]

On 2003-01-15 20:31, hinchah wrote:
i have 100 rows of data. when i use the autofilter my data changes to 15 rows. how can i write the median formula in cell B16 to find the median of the 15 rows of filtered data?? i am currently using this formula to find the average, =subtotal(1,B1:B100), but i cant figure out a way to do it for the median.

thanks

Hi hinchah:

Since the SUBTOTAL MEDIAN computation is not available natively, I have made use of the SUBTOTAL (count) computation to compute the MEDIAN value of the filtered set using the INDEX function. See the following simulation:

y030115h1.xls
	A	B	C	D
4
5		6.5	MedianCalculation
6
7		1
8		2
9		3
10		4
11		5
12		6	Rangetobefiltered
13		7
14		8
15		9
16		10
17		11
18		12
Sheet4

</SPAN>

Please post back if it works for you -- otherwise explain a little further and let us take it from there.

 

[hinchah]

i do not wish to use the subtotal function to find the median. i was just wondering if it was possible to find the median after auto filtering my data. because if i write the formula =median(B1:B100)in cell B101, and then run the auto filter which filters my data into 15 rows, i get the median of rows B1 through B100. I only want the median of the 15 rows that have been filtered. So for example: after the auto filter, i want to write the median formula to only take the median of the random data in column B which makes up the 15 rows that have been filtered.

thanks

 

[Yogi Anand]

On 2003-01-15 22:11, hinchah wrote:
i do not wish to use the subtotal function to find the median. i was just wondering if it was possible to find the median after auto filtering my data. because if i write the formula =median(B1:B100)in cell B101, and then run the auto filter which filters my data into 15 rows, i get the median of rows B1 through B100. I only want the median of the 15 rows that have been filtered. So for example: after the auto filter, i want to write the median formula to only take the median of the random data in column B which makes up the 15 rows that have been filtered.

thanks

Hi hinchah:

Since there is no MEDIAN value available to be computed for the filtered data, I have given you a way around that would compute the MEDIAN value of the filtered data. Since the use of the SUBTOTAL function with its number of attributes can operate on the filtered data, in my solution I have made use of the SUBTOTAL and the INDEX functions. In the example that I have posted, if you filter that data on some criterion, the formula will compute the MEDIAN of the filtered set of records.

I hope this helps.

 

[hinchah]

yogi, i am trying the formula on this data and i am getting the results below:

-41.55%
-5.60%
-61.46%
-5.30%
-17.89%

using this formula:

=(INDEX(C1:C5,INT(SUBTOTAL(2,C1:C5)/2)+IF(ISODD(SUBTOTAL(2,C1:C5)),1,0))+INDEX(C1:C5,INT(SUBTOTAL(2,C1:C5)/2)+1))/2

and it is pulling in -61.46%, when it should be pulling in -17.89%

what do you think??

thanks

 

[Yogi Anand]

Hi hinchah:

Median is that value in a set which lies midway between the set -- nominally half the values are larger, and half the values are smaller than the Median value. So, my suggestion would be that you sort your data before filtering and then applying my formula will give the right result --see the following simulation:

y030115h1.xls
	A	B	C	D
4
5		-0.1789	MedianCalculation
6
7		-5.30%
8		-5.60%	Rangetobefiltered
9		-17.89%
10		-41.55%
11		-61.46%
Sheet4 (2)

</SPAN>

 

[PaddyD]

just another option:

say you had some data in a1:b10, such that after the application of a filter you were left with the %s the OP posted visable in col B. array enter the following (using control + shift + enter, not just enter):

=MEDIAN(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),,1)),B2:B10,""))

...where the subtotal / offset combination os used to generate an array that indicates which of the values in the original range are still visable.

paddy

 

[Tom Urtis]

And yet another option using VBA:

Sub MedianFiltered()
Dim MedianCells As Range
Set MedianCells = Range("B1:B100")
Range("B101").Value = Application.WorksheetFunction.Median(MedianCells.SpecialCells(xlCellTypeVisible))
End Sub

 

[hinchah]

yogi,

when i use the following data:

Fund_ID Period_Date L_TWR
245 6/30/1993 0.00%
431 6/30/1993 0.00%
2428 6/30/1993 0.00%
556 6/30/1993 -1.83%
533 6/30/1993 -2.80%
358 6/30/1993 -4.32%
879 6/30/1993 -6.58%
366 6/30/1993 -13.87%
360 6/30/1993 -23.89%

along with the formula:

=(INDEX($C$1:$C$10275,INT(SUBTOTAL(2,$C$1:$C$10275)/2)+IF(ISODD(SUBTOTAL(2,$C$1:$C$10275)),1,0))+INDEX($C$1:$C$10275,INT(SUBTOTAL(2,$C$1:$C$10275)/2)+1))/2

i get the wrong median, 0.0%. but when i write the formula like the following:

=(INDEX(C4:C12,INT(SUBTOTAL(2,C4:C12)/2)+IF(ISODD(SUBTOTAL(2,C4:C12)),1,0))+INDEX(C4:C12,INT(SUBTOTAL(2,C4:C12)/2)+1))/2

i get the correct median, -2.8%?

in the first formula i am using such a large range due to the fact that my data takes up rows 1 thru 10275, but i am really confused because the subtotal count function should allow me to put in the large range?? correct? such as my average calculation =subtotal(1,$C$1:$C$10275)??

can you help me out and maybe offer some reasons as to why it will not calculate correctly when i have the range in absoulte value format ($C$1:$C$10275)??

thanks