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

On Jul 25, 2:09 pm, Geoff wrote:
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.

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.

On Jul 25, 8:22 pm, wrote:
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,
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"
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

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 wrote:
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.

On Jul 25, 8:22 pm, wrote:
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.

Hi Jim

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

Geoff
On Jul 25, 8:47 pm, "Jim Cone" wrote:
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, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

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

On Jul 25, 9:30 pm, Alan Beban <unavailable wrote:
If the functions in the frely downloadable file athttp://home.pacbell.net/bebanare available to your workbook

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

Alan Beban

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





"Geoff" wrote:

Hi Jim

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

Geoff
On Jul 25, 8:47 pm, "Jim Cone" wrote:
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, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

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

On Jul 26, 2:04 am, JMB wrote:
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).

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" wrote:

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

On Jul 26, 2:04 am, JMB wrote:
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).

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

On Jul 27, 12:56 am, JMB wrote:
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.

Glad that worked for you. Thanks for posting back.

"Geoff" wrote:

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

On Jul 27, 12:56 am, JMB wrote:
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.