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

1. ## 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. ## 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. ## 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.

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. ## 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. ## 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
•