Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    absolute values of array elements (XL 97 sr2 on Win 2000)

    I have a VBA array holding a set of numbers, some positive, some negative. I can get the median value with:
    <font face="Georgia">Application.WorksheetFunction.Media n(myarray)</font face=georgia>

    but what I really want is the median of the absolute values, i.e. ignoring the sign of the number in each element.

    (I'm)stuck

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: absolute values of array elements (XL 97 sr2 on Win 2000)

    Unless you need the signed values for other purposes, you could assign the abolute value when you are populating the array with =Abs(x), where x is the value.

    Otherwise you probably need a secoond array to hold the absolute values, where you could try something like :

    MyArray2 = MyArray1
    For i = 0 To UBound(MyArray2)
    MyArray2(i) = Abs(MyArray2(i))
    Next
    x = (Application.WorksheetFunction.Median(MyArray2))
    Erase MyArray2

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: absolute values of array elements (XL 97 sr2 on Win 2000)

    Thank you, I sort of got there as well and, as I need the original values, have employed a second array.

    I'm curious about your use of the line:
    MyArray2 = MyArray1

    Is this a genuine piece of VBA code or just shorthand to indicate the need to create a copy of the original by copying each element across one at a time via a loop?

    I ask because when I tried it 'as is' I got an error message (this is VBA from XL 97 remember).

    (not as) stuck (as I was)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: absolute values of array elements (XL 97 sr2 on Win 2000)

    I have XL97 and that syntax works for me. Here is the testcode I used.

    Steve

    <pre>Function MedianAV(myArray)
    myArray2 = myArray
    For i = 0 To UBound(myArray2)
    myArray2(i) = Abs(myArray2(i))
    Next
    MedianAV = (Application.WorksheetFunction.Median(myArray2))
    Erase myArray2
    End Function
    </pre>


  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: absolute values of array elements (XL 97 sr2 on Win 2000)

    <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> It's not fair, it DIDN'T work when I tried it late yesterday but you are correct it works fine.

    Moral: never go exploring off the edge of the map late in the day / when you are not at your sharpest.

    (un)stuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •