Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Up A Collection
I have some code that is passed a Collection of positive values. What is the
easiest way to average the values ? -- Gary''s Student - gsnu201001 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Up A Collection
Is your collection a collection of range values? That's what I assumed. Not
sure if this is the easiest way, but it worked for me. Sub test() Dim coll As Collection Set coll = New Collection With coll .Add Range("A1").Value .Add Range("A2").Value .Add Range("A3").Value .Add Range("A4").Value End With MsgBox AvgCollection(coll) End Sub Function AvgCollection(col As Variant) As Double Dim c As Variant Dim dblSum As Double Dim counter As Long For Each c In col dblSum = dblSum + c counter = counter + 1 Next c AvgCollection = dblSum / counter End Function -- Cheers, Ryan "Gary''s Student" wrote: I have some code that is passed a Collection of positive values. What is the easiest way to average the values ? -- Gary''s Student - gsnu201001 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Up A Collection
Two things... first, I would probably declare the argument to your
AvgCollection function as Collection instead of Variant and, second, you don't need to maintain a separate Counter to count the elements in the Collection as Collections have a Count property. This is how I would modify your function... Function AvgCollection(col As Collection) As Double Dim c As Variant Dim dblSum As Double For Each c In col dblSum = dblSum + c Next AvgCollection = dblSum / col.Count End Function -- Rick (MVP - Excel) "Ryan H" wrote in message ... Is your collection a collection of range values? That's what I assumed. Not sure if this is the easiest way, but it worked for me. Sub test() Dim coll As Collection Set coll = New Collection With coll .Add Range("A1").Value .Add Range("A2").Value .Add Range("A3").Value .Add Range("A4").Value End With MsgBox AvgCollection(coll) End Sub Function AvgCollection(col As Variant) As Double Dim c As Variant Dim dblSum As Double Dim counter As Long For Each c In col dblSum = dblSum + c counter = counter + 1 Next c AvgCollection = dblSum / counter End Function -- Cheers, Ryan "Gary''s Student" wrote: I have some code that is passed a Collection of positive values. What is the easiest way to average the values ? -- Gary''s Student - gsnu201001 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Up A Collection
The reason I used Variant was because I wasn't totally sure what he meant by
"a collection of positive values". I assumed he had a collection of cell values, but that it could be possible to have a collection of values in an Array. But you are right, in my example I should have used the syntax in your post. Thanks for the tips! They are always welcome. -- Cheers, Ryan "Rick Rothstein" wrote: Two things... first, I would probably declare the argument to your AvgCollection function as Collection instead of Variant and, second, you don't need to maintain a separate Counter to count the elements in the Collection as Collections have a Count property. This is how I would modify your function... Function AvgCollection(col As Collection) As Double Dim c As Variant Dim dblSum As Double For Each c In col dblSum = dblSum + c Next AvgCollection = dblSum / col.Count End Function -- Rick (MVP - Excel) "Ryan H" wrote in message ... Is your collection a collection of range values? That's what I assumed. Not sure if this is the easiest way, but it worked for me. Sub test() Dim coll As Collection Set coll = New Collection With coll .Add Range("A1").Value .Add Range("A2").Value .Add Range("A3").Value .Add Range("A4").Value End With MsgBox AvgCollection(coll) End Sub Function AvgCollection(col As Variant) As Double Dim c As Variant Dim dblSum As Double Dim counter As Long For Each c In col dblSum = dblSum + c counter = counter + 1 Next c AvgCollection = dblSum / counter End Function -- Cheers, Ryan "Gary''s Student" wrote: I have some code that is passed a Collection of positive values. What is the easiest way to average the values ? -- Gary''s Student - gsnu201001 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Up A Collection
Thanks Rick!
-- Gary''s Student - gsnu201001 "Rick Rothstein" wrote: Two things... first, I would probably declare the argument to your AvgCollection function as Collection instead of Variant and, second, you don't need to maintain a separate Counter to count the elements in the Collection as Collections have a Count property. This is how I would modify your function... Function AvgCollection(col As Collection) As Double Dim c As Variant Dim dblSum As Double For Each c In col dblSum = dblSum + c Next AvgCollection = dblSum / col.Count End Function -- Rick (MVP - Excel) "Ryan H" wrote in message ... Is your collection a collection of range values? That's what I assumed. Not sure if this is the easiest way, but it worked for me. Sub test() Dim coll As Collection Set coll = New Collection With coll .Add Range("A1").Value .Add Range("A2").Value .Add Range("A3").Value .Add Range("A4").Value End With MsgBox AvgCollection(coll) End Sub Function AvgCollection(col As Variant) As Double Dim c As Variant Dim dblSum As Double Dim counter As Long For Each c In col dblSum = dblSum + c counter = counter + 1 Next c AvgCollection = dblSum / counter End Function -- Cheers, Ryan "Gary''s Student" wrote: I have some code that is passed a Collection of positive values. What is the easiest way to average the values ? -- Gary''s Student - gsnu201001 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Take Up A Collection
Thanks!
-- Gary''s Student - gsnu201001 "Ryan H" wrote: Is your collection a collection of range values? That's what I assumed. Not sure if this is the easiest way, but it worked for me. Sub test() Dim coll As Collection Set coll = New Collection With coll .Add Range("A1").Value .Add Range("A2").Value .Add Range("A3").Value .Add Range("A4").Value End With MsgBox AvgCollection(coll) End Sub Function AvgCollection(col As Variant) As Double Dim c As Variant Dim dblSum As Double Dim counter As Long For Each c In col dblSum = dblSum + c counter = counter + 1 Next c AvgCollection = dblSum / counter End Function -- Cheers, Ryan "Gary''s Student" wrote: I have some code that is passed a Collection of positive values. What is the easiest way to average the values ? -- Gary''s Student - gsnu201001 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
collection obj | Excel Programming | |||
Sum a collection | Excel Programming | |||
Collection Key | Excel Programming | |||
Collection | Excel Programming |