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 <gjn...@gmail.com> 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, carlos_ra...@hotmail.com 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" <(E-Mail Removed)>
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, carlos_ra...@hotmail.com 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" <jim.cone...@rcn.comXXX> 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" <jim.cone...@rcn.comXXX> 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 <J...@discussions.microsoft.com> 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 <J...@discussions.microsoft.com> 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).
>
>