Need Properties Of New Collection

[davidm]

Sub BuildUniqueItems()
Dim x as New collection

On error resume next
For each c in [a1:g1000]
x.Add c, CStr(c.value)
Next
Counter=x.Count
Avg=x.Average
Std=x.Stdev
Mode =x.Mode 'to cross-test uniqueness

End sub

To my bewilderment, the Avg, std, mode return errors. Any help would be appreciated.

David

[rvExcelNewTip]

You should not be bewilderd - a Collection does not have these properties.

A collection is a bunch of items that are not necessarily numbers: e.g. Worksheets (with ending s) is a Collection. You can count the number of worksheets in the Worksheets collection, but it is hard to average them; Therefore the only property that a standard collection has, is a Count property.

[rvExcelNewTip]

As an afterttought, why don't you just use

=count(a1:g1000)
=average(a1:g1000)
=stdev(a1:g1000)
=mode(a1:g1000)

For the latter you may need to active the Analysis Toolpack Add-In: Extra -> Add-Ins-> Select analysis Toolpack

[davidm]

Rv,

Thanks for the help. The idea however is not to simply get the summary statistics of the values in the range "a1:g1000" but to draw out the vital stats of the UNIQUE VALUES. If the items processed by the New Collection method are strings with "numeric character", such as in the example, it would be great if the method could allow for some kind data manipulation. I now assume only COUNT works

DM

[rvExcelNewTip]

I see. So you add the items to a collection to weed out the duplicate ones. I'm afraid that in order to calculate let's say, the average, you will have to loop again through that collection and perform the addition and then use the Collection.count to obtain the average.

Standard deviation should be calculated in a similar way.

Mode should be undefined! Because the collection is a set of UNIQUE items, all of them occur with the same frequency i.e. 1