Answered by:
Finding Quartiles From 2 Dimensional Array Using Excel's Quartile Function

Greetings Everyone:
I am trying to use Excel's Quartile Function in VBE to find the quartiles in the Columns of a 2 dimensional array.
I am having trouble getting my mind around this, I think due to the fact that I do not know how to define each column as a range so that it can be used in the Excel Quartile Function in the form =Quartile(Range, 1), where 1 is the first quartile in the range.
Is there a way to find the quartiles in a 2 dimensional array using this function on the range in the array of say (1,1:5,1)?
Or more broadly, how do I find the quartiles in the columns of a 2 dimensional array?
Thanks if advance for any help or ideas.
Glenn
Question
Answers

This works for me. Returning 2 when testing values 1 and 5.
Dim vntArray(1, 1)
Dim vntHoldArray(1)
vntArray(0, 0) = 1
vntArray(0, 1) = 1
vntArray(1, 0) = 5
vntArray(1, 1) = 1
vntHoldArray(0) = vntArray(0, 0)
vntHoldArray(1) = vntArray(1, 0)
MsgBox Application.WorksheetFunction.Quartile(vntHoldArray, 1) 
Andy,
That definitely worked when I adapted it to my code.
Here's the actual code that I used if anyone is interested:
Thanks again very much for your help.
Glenn
For i = 1 To Years
For x = 1 To Iterations
QuartileHolding(x) = PortVal(x, i)
Next
For x = 1 To 4
Quartiles(x, i) = (objExcel.Application.WorksheetFunction.Quartile(QuartileHolding, x))
Next
Next
For i = 1 To Years
For x = 1 To 4
MsgBox("Quartiles " & x & " " & i & "= " & Quartiles(x, i))
Next
Next
For x = 1 To Years
QuartileLine1(x) = Quartiles(1, x)
QuartileLine2(x) = Quartiles(2, x)
QuartileLine3(x) = Quartiles(3, x)
QuartileLine4(x) = Quartiles(4, x)
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine1(x))
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine2(x))
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine3(x))
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine4(x))
Next
All replies



Andy,
Thanks for the answer, but I am trying to select a range in Visual Basic Express 2005, for example and entire column in a virtual 2 dimensional array and then use the quartile function as follows:
Quartile1 = Application.WorksheetFunction.Quartile(Array,1), to get the first quartile of the numbers contained in Array.
I have tried this several ways as shown below and nothing works. The exact same function syntax will work for the "Average" function, however.
This seems to be a VBE question, but Bruno move it here in hopes of a better response.
Any more ideas?
Thanks in advance.
Glenn
For x = 1 To Iterations
QuartileHolding(x, 1) = PortVal(x, 1)
MsgBox("Quartile Holding " & x & " = " & QuartileHolding(x, 1))
Next
Quartiles(1, 1) = (objExcel.Application.WorksheetFunction.Quartile(QuartileHolding, 1))
MsgBox("Quartiles 1 1 = " & Quartiles(1, 1))
For x = 1 To Iterations
For i = 1 To Years
MsgBox("MyArray " & x & " " & i & " = " & MyArray(x, i))
Quartiles(i, 1) = (objExcel.Application.WorksheetFunction.Quartile(PortVal(x, Iterations), 1))
Quartiles(i, 2) = (objExcel.Application.WorksheetFunction.Quartile(PortVal(x, Iterations), 2))
Quartiles(i, 3) = (objExcel.Application.WorksheetFunction.Quartile(PortVal(x, Iterations), 3))
Quartiles(i, 4) = (objExcel.Application.WorksheetFunction.Quartile(PortVal(x, Iterations), 4))
Next
Next
For x = 1 To 4
For i = 1 To Years
MsgBox("Quartiles " & x & " " & i & " = " & Quartiles(x, i))
Next
Next

This works for me. Returning 2 when testing values 1 and 5.
Dim vntArray(1, 1)
Dim vntHoldArray(1)
vntArray(0, 0) = 1
vntArray(0, 1) = 1
vntArray(1, 0) = 5
vntArray(1, 1) = 1
vntHoldArray(0) = vntArray(0, 0)
vntHoldArray(1) = vntArray(1, 0)
MsgBox Application.WorksheetFunction.Quartile(vntHoldArray, 1) 
Andy,
That definately works as advertised. Thank you very much for your help.
I must be doing something else wrong, but I haven't had a chance to check it out yet. I think I was trying to use a 2 dimensional array as the first argument in the Quartile function.
I'll adapt this to my actual code and see if it works, but I'm sure it will.
Thanks again for your help.
Glenn

Andy,
That definitely worked when I adapted it to my code.
Here's the actual code that I used if anyone is interested:
Thanks again very much for your help.
Glenn
For i = 1 To Years
For x = 1 To Iterations
QuartileHolding(x) = PortVal(x, i)
Next
For x = 1 To 4
Quartiles(x, i) = (objExcel.Application.WorksheetFunction.Quartile(QuartileHolding, x))
Next
Next
For i = 1 To Years
For x = 1 To 4
MsgBox("Quartiles " & x & " " & i & "= " & Quartiles(x, i))
Next
Next
For x = 1 To Years
QuartileLine1(x) = Quartiles(1, x)
QuartileLine2(x) = Quartiles(2, x)
QuartileLine3(x) = Quartiles(3, x)
QuartileLine4(x) = Quartiles(4, x)
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine1(x))
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine2(x))
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine3(x))
Next
For x = 1 To Years
MsgBox("Quartile Line " & x & " = " & QuartileLine4(x))
Next