Dynamic Range

[HMN93]

Hello,
I would like to ask for help on this issue.
I have a list of items and cost and I need to find the median for every set of item
example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]Cost[/TD]
[TD]Median[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]3[/TD]
[TD]apple[/TD]
[TD]3.75[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]2.8[/TD]
[TD]orange[/TD]
[TD]4.3[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]5.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]4.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]2.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]4.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]5.7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]4.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]3.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]6.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]3.9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to do this in excel vba
how can I write the macro to find the median for each item?
I thought of a for loop but I am stuck when it comes to how to write the range so I will be dynamic

Any help is greatly appreciated!

 

[DRSteele]

Do you require VBA? There is a simple formula solution which I'll provide in case it suffices.

Does this meet your needs?

<

Excel 2012
	A	B	C	D	E	F
1	item	Cost			Median	Median
2	apple	3		apple	3.9	3.9
3	apple	2.8		orange	4.3	4.3
4	apple	5.3		pear	51	51
5	apple	4.5		mango	110	110
6	orange	2.3
7	orange	4.3
8	orange	5.7
9	orange	4.3
10	orange	3.5
11	orange	6.1
12	orange	3.9
13	pear	51
14	mango	110
Sheet49

Cell Formulas
Range	Formula
E2	=SUMIFS($B$2:$B$14,$A$2:$A$14,D2)/COUNTIFS($A$2:$A$14,D2)
E3	=SUMIFS($B$2:$B$14,$A$2:$A$14,D3)/COUNTIFS($A$2:$A$14,D3)
E4	=SUMIFS($B$2:$B$14,$A$2:$A$14,D4)/COUNTIFS($A$2:$A$14,D4)
E5	=SUMIFS($B$2:$B$14,$A$2:$A$14,D5)/COUNTIFS($A$2:$A$14,D5)
F2	=SUMIFS($B:$B,$A:$A,D2)/COUNTIFS($A:$A,D2)
F3	=SUMIFS($B:$B,$A:$A,D3)/COUNTIFS($A:$A,D3)
F4	=SUMIFS($B:$B,$A:$A,D4)/COUNTIFS($A:$A,D4)
F5	=SUMIFS($B:$B,$A:$A,D5)/COUNTIFS($A:$A,D5)

 

[HMN93]

DRSteele, thank you for your reply.
doesn't the sum/count return the mean? As for using vba, the next step is to calculate MAD (median absolute deviation) and this computation is part of a long excel sheet and I need to use vba to compute everything.

 

[DRSteele]

Yes, it counts mean. I misread - my apologies. I cannot help with VBA...sorry and good luck.