Can you calculate the Median without each value in separate cells?

[Dreiding]

Using Excel 2003 I have a table with two columns for frequency distribution.
The column labels are BID and COUNT:
BID COUNT
5 0
10 2
15 1
20 2
25 5
30 1

I calculate the historgram data AVERAGE using
=SUMPRODUCT($A$2:$A$7,$B$2:$B$7)/SUM($B$2:$B$7)

Is there an easy way to identify the MEDIAN an avoid populating cells with
this data before using the median function?

Thanks,
- Pat

 

[Joel]

You can call most of the worksheet functions from VBA like this:

WorksheetFunction.Median()
WorksheetFunction.Average()
WorksheetFunction.StDev()


To find which functions are uspported type the following

a = worksheetfunction.

When you type the period on the line a drop down box will appear showing you
all the worksheet functions that are avbbailable in VBA.

 

[Dreiding]

Hi Joel,

Thanks for the response, but you misunderstood my question.
Let me give you a scaled down example.

I have 11 numbers in 11 cells. They are
{5,5,10,10,10,10,10,10,10,10,10,10}. Using the MEDIAN function Excel will
return a 10.

My question is: what method (formula?) can I use to do this calculate when I
have this data documented in two columns. One column is the value, the other
is the number of occurances.
VALUE OCCURANCE
5 2 'equates to {5, 5}
10 9 'equates to {10,10,10,10,10,10,10,10,10,10}

Media is still 10, but how do I calculate this from the two columns? How?

Thanks,
- Pat

 

[Joel]

Here is a UDF that will return the array you are looking for

Call with the Range of your table =Median(GetArray(A1:B10))

Function GetArray(Target As Range) As Variant

Dim Results() As Variant

NumRows = Target.Rows.Count
'The sum of the 2nd column is the number of items to return
ArraySize = WorksheetFunction.Sum(Target.Columns(2))

ReDim Results(ArraySize)
Count = 1
For RowCount = 1 To NumRows
For RepeatCount = 1 To Target(RowCount, 2)
Results(Count) = Target(RowCount, 1)
Count = Count + 1
Next RepeatCount
Next RowCount
GetArray = Results

End Function

 

[Joel]

Just did some checking and found it return an extra 0 at the beginning of the
array. Try this instead

Function GetArray(Target As Range) As Variant

Dim Results() As Variant

NumRows = Target.Rows.Count
NumCol = Target.Columns.Count
'Sum of 2nd column is size of array
ArraySize = WorksheetFunction.Sum(Target.Columns(2))

ReDim Results(ArraySize - 1)
Count = 0
For RowCount = 1 To NumRows
For RepeatCount = 1 To Target(RowCount, 2)
Results(Count) = Target(RowCount, 1)
Count = Count + 1
Next RepeatCount
Next RowCount
GetArray = Results

End Function

 

[Dreiding]

Thanks Joel,

You gave me enough information to create a custom "FrequencyMedian" function
to do the job I need.
Thanks -Pat