creating a small array from spreadsheet values/counts
 

Hi,

I need to create an array in VBA and then perform a MEDIAN function on
it.

The data in the sheet which will be used to create the array is as
follows:

A B
2 1
3 2
1 3
2 4
3 5

Column B refers to a VALUE, Column A refers to the COUNT of the number
of instance of that value in the desired array.

ie the array (1-dimensional list really) actually looks like:

1
1
2
2
2
3
4
4
5
5
5

What i am trying to do is create a FUNCTION which uses the ranges in
columns A&B to create this array and the return the MEDIAN of the array
back (in this small example, the MEDIAN is 3).

Any ideas please?

Thanks in Advance,

Tigpup

creating a small array from spreadsheet values/counts
 

Hi tigpup,
Try this function. The two ranges are inputted as one, so the left
column has to be the frequencies and the right column the values as in
your example data.

Public Function MEDIAN2(DataArray As Range) As Single
Application.Volatile
Dim MedianArray() As Single
Dim I As Integer
Dim J As Integer
Dim K As Long
Dim iArrayRows As Long
Dim iDataRows As Long
Dim iSheetRows As Long
iSheetRows = DataArray.Rows.Count
iArrayRows = WorksheetFunction.Sum(DataArray. _
Range(Cells(1, 1), Cells(iSheetRows, 1)))
ReDim Preserve MedianArray(1 To iArrayRows)
For I = 1 To iSheetRows
For J = 1 To DataArray.Cells(I, 1).Value
K = K + 1
MedianArray(K) = DataArray.Cells(I, 2).Value
Next J
Next I
If (UBound(MedianArray) Mod 2) = 0 Then
Let MEDIAN2 = (MedianArray(UBound(MedianArray) / 2) _
+ MedianArray(UBound(MedianArray) / 2 + 1)) / 2
Else: Let MEDIAN2 = MedianArray(UBound(MedianArray) / 2 + 0.5)
End If
End Function

I couldn't call it MEDIAN, that name clashed with the standard MEDIAN
function.

Ken Johnson

creating a small array from spreadsheet values/counts
 

If you go back to basics, the definition of median is the value that has 1/2
the population on either side of it. So, what you need to do is a running
total of the values in A until you get to SUM({col. A values})/2

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hi,

I need to create an array in VBA and then perform a MEDIAN function on
it.

The data in the sheet which will be used to create the array is as
follows:

A B
2 1
3 2
1 3
2 4
3 5

Column B refers to a VALUE, Column A refers to the COUNT of the number
of instance of that value in the desired array.

ie the array (1-dimensional list really) actually looks like:

1
1
2
2
2
3
4
4
5
5
5

What i am trying to do is create a FUNCTION which uses the ranges in
columns A&B to create this array and the return the MEDIAN of the array
back (in this small example, the MEDIAN is 3).

Any ideas please?

Thanks in Advance,

Tigpup

creating a small array from spreadsheet values/counts
 

'--------------------------
Sub CreateNumbers()
Dim rngSelection As Excel.Range
Dim rngCell As Excel.Range
Dim lngResult As Double
Set rngSelection = Excel.Selection

For Each rngCell In rngSelection.Columns(2).Cells
rngCell.Resize(1, rngCell(1, 0).Value).Value = rngCell.Value
Next
lngResult = Application.Median(rngSelection.Columns(2).Resize _
(, Application.Max(rngSelection.Columns(1))))
MsgBox lngResult
End Sub
'------------------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"tigpup." wrote in message
Hi,
I need to create an array in VBA and then perform a MEDIAN function on
it.
The data in the sheet which will be used to create the array is as
follows:

A B
2 1
3 2
1 3
2 4
3 5

Column B refers to a VALUE, Column A refers to the COUNT of the number
of instance of that value in the desired array.
ie the array (1-dimensional list really) actually looks like:

1
1
2
2
2
3
4
4
5
5
5

What i am trying to do is create a FUNCTION which uses the ranges in
columns A&B to create this array and the return the MEDIAN of the array
back (in this small example, the MEDIAN is 3).
Any ideas please?
Thanks in Advance,
Tigpup

creating a small array from spreadsheet values/counts
 

Ken,

This is fantastic - many thanks.

It has also renewed my interest in VBA coding - i'll read the code
carefully and SLOWLY try to make full sense of it.

This is really appreciated.

Best Regards,

Tigpup

creating a small array from spreadsheet values/counts
 

Hi Tigpup,
You're welcome. Thanks for the feedback.
I guess you're aware that the result of the function is only correct
when column B (values) are in order (ascending or descending)
Ken Johnson

creating a small array from spreadsheet values/counts
 

Hi Tigpup,
Just an after-thought, I and J really should be dimensioned as Long, as
is K, just in case a user selects more than 32,767 rows ( the upper
limit for integer variable).

Ken Johnson

creating a small array from spreadsheet values/counts
 

Thanks to everyone for all your valuable help.

Issue has been resolved.

Regards,

Tigpup