# Finding Quartiles From 2 Dimensional Array Using Excel's Quartile Function

### Question

• 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

Monday, April 30, 2007 1:24 AM

• 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)
Tuesday, May 08, 2007 9:19 AM
• 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

Wednesday, May 09, 2007 2:02 AM

### All replies

•

Move the thread from Visual Basic Express Edition in order to get better answers.

Monday, May 07, 2007 11:26 AM
• Hi,

Given test data, the function will return the answer 2 into C1

A1: =1     B1: =1
A2: =5:    B2: =1

Range("C1") = Application.WorksheetFunction.Quartile(Range("A1:B2").Columns(1), 1)

Monday, May 07, 2007 12:00 PM
• 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?

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

Tuesday, May 08, 2007 12:05 AM
• 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)
Tuesday, May 08, 2007 9:19 AM
• 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.

Glenn

Tuesday, May 08, 2007 11:40 PM
• 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

Wednesday, May 09, 2007 2:02 AM