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

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

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

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