GAtkins

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

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

Bruno Yu - MSFT

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

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

Andy Pope

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)

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

GAtkins

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

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

Andy Pope

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)

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

GAtkins

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

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

GAtkins

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