none
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

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)
    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?

     

    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

    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.

     

    Thanks again for your help.

     

    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