How to automate the calculation of the median from a frq distribut

[Alaska Hydro]

Instructions for calculating the median froma frequency distribution tend to
rely on a visual interpretation of the frequency distribution. I have
hundreds of frequency distributions that I want to calculate the median for.

Can I automate the calculation of the median directly using code or do I
need to transform the distribution temporarily into its original array then
calc?

I can provide examples if necessary.
thanks.

 

[N10]

Hi Alaska

I think this would work if you placed your array in column A. It uses the
formula (n+1) /2 as the estimator for the median

Dim datarange As Range


Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim datarange As Range

Set datarange = Selection

k = datarange.Rows.Count + 1
k = k / 2

Range("a1").Select
For x = 1 To k
ActiveCell.Offset(1, 0).Select
Next

MsgBox (" THE MEDIAN IS ") & ActiveCell.Value


Hope this helps

Best N10

 

[Alaska Hydro]

Thanks N10 - your formula would work for an array - alas I have frequency
distributions that look like this:
class (mm) count
1 11
2 6
4 2
8 6
16 1
32 21
64 35
128 11
256 0
512 7
1024 0
2048 0
4096 0
8192 0

The count category represents the number of hits in a particular class.
Were this an array it would look like:
1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,4,4,... ...,512 with an n of 100. The
median value falls in the 64 category.
One thought I had would be to write the distribution back into an array
temporarily - calculate the median as you so helpfully wrote below, then
delete the temporary array and continue to the next set. It might be nicer
to have it work on the distribution itself though.
thanks.