average of array element

[Geoff]

Hi All

I am trying to clean up a huge amount of data for further analysis.

One of the steps is to remove outliers. I have chosen to work with a
normal distribution and so want to delete all values that fall outside
of 3 deviations from the mean.

Application.Average(Application.Index(myArray, 0, c)) (c is the
column of the array)

Gives me the average but I have already deleted non numeric values and
also incorrect duplicate values. VBA for excel fills the empty pieces
in the array with zeros so my average is not correct. If I do this in
excel the average will exclude empty cells but in the array it does
not do this.

Is there a simple way to work around this.

Thank you in advance

Geoff

 

[carlos_ray86]

Hi All

I am trying to clean up a huge amount of data for further analysis.

One of the steps is to remove outliers. I have chosen to work with a
normal distribution and so want to delete all values that fall outside
of 3 deviations from the mean.

Application.Average(Application.Index(myArray, 0, c)) (c is the
column of the array)

Gives me the average but I have already deleted non numeric values and
also incorrect duplicate values. VBA for excel fills the empty pieces
in the array with zeros so my average is not correct. If I do this in
excel the average will exclude empty cells but in the array it does
not do this.

Is there a simple way to work around this.

Thank you in advance

Geoff

I'm not sure if this will help but here is one way to delete all of
those zeros so then you can look at just the non-zero values
Dim myCell As Range
For Each myCell In Worksheets("Sheet1").Range("A1:BH100")
If myCell.Value = "0" Then
myCell.Clear
End If
Next myCell

you can change the clear to delete if you want the deleted. Hope it
helps.

 

[Geoff]

Your suggestion works in a spreadsheet but not within an array the
array fills the empty cell with 0 not as empty. maybe I am
dimensioning my array incorrectly.

Thanks for the response.

 

[Jim Cone]

Geoff,
I bet your array is declared as a numeric data type.
If so the default value of all elements is 0 (zero).
Declare the array as a Variant to have a truly empty array.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Geoff" <[email protected]>
wrote in message
Hi All
I am trying to clean up a huge amount of data for further analysis.
One of the steps is to remove outliers. I have chosen to work with a
normal distribution and so want to delete all values that fall outside
of 3 deviations from the mean.
Application.Average(Application.Index(myArray, 0, c)) (c is the
column of the array)
Gives me the average but I have already deleted non numeric values and
also incorrect duplicate values. VBA for excel fills the empty pieces
in the array with zeros so my average is not correct. If I do this in
excel the average will exclude empty cells but in the array it does
not do this.
Is there a simple way to work around this.
Thank you in advance
Geoff

 

[Alan Beban]

If the functions in the frely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Application.Sum(myArray)/(ArrayCount(myArray)-ArrayCountIf(myArray, 0))

Alan Beban

 

[Geoff]

Hi Jim

It is dimensioned as Variant as it originally contains both string and
numeric data.

Geoff

 

[Geoff]

Hi Alan

Thanks I will try this and give feedback. Almost 10:30 pm here in
south africa so I will do it tomorrow morning.

Geoff

 

[JMB]

How are you getting the data into the array in the first place? I entered
some data on a worksheet (leaving some empty cells), put those values in an
array and got the same answer in VBA as I do in excel (so the blanks in my
example are not being treated as zeros).

So far, I can only duplicate your problem if I use a type conversion
function, such as CLng, when the data gets loaded into the array, which will
convert the blank cells to 0's in the array. Is your data is being affected
by some explicit or implicit (as Jim said) type conversion?

You said you remove non-numeric data from the array. How is that done? If
you are using Isnumeric to test array elements prior to using some type
conversion function, include a test Len(arrayelement)>0 as isnumeric of an
empty element will return True (because it can be coverted to 0).

Hi Jim

It is dimensioned as Variant as it originally contains both string and
numeric data.

Geoff

 

[Geoff]

You are correct I am doing a Isnumeric test to remove non numeric
values. I then set the array element to vbempty. I tried clearing the
element but that did not work or rather I could not get the syntax to
work. vbempty removes the data but when I paste the array back into
the spreadsheet all the cleared cells contain zeros. I thought it was
beacuse I was using the following syntax for writing the array
back myRange.Value = myArray
myRange is the original data range contianing non-numeric values and
easily identified outliers such as values of 10^23. The data is form a
paper machine process control system that I need to massage.

I am fairly new to VBA and very new to arrays. If you would like to
see the whole code of the macro I will paste it into a message for you
to look at.

Thank you

 

[JMB]

If it doesn't create a problem for any other areas of your code, perhaps use
vbNullString instead of vbEmpty. The underlying numeric value of vbEmpty is
zero. And average ignores text anyway - so it shouldn't create a problem
there.

 

[Geoff]

vbnullstring solved the problem I am now getting the results I want.
Thank very much for your help.

 

[JMB]

Glad that worked for you. Thanks for posting back.