I have a list of differerent-sized groups and values in a table. I am
trying to determine the Average and Stdev for each group. The formula
would need to reference the entire data range since the number of data
points in each group is continually changes.

For example in Col A1:B8 ....


Col A Col B
Group 1 500
Group 1 230
Group 1 102
Group 1 402
Group 2 300
Group 2 222
Group 3 134
Group 3 153

On the spreadsheet level, I can do a formula like:

=AVERAGE(IF(A1:A8="Group 1",B1:B8))
(entered as an array formula)

Is there a way to do a similar one-liner in VBA to get the same
results? I can't figure out how to enter an "array" type formula in
VBA.

Thanks.


John

VBA does not have array formulas, but Evaluate() will pass a string to Excel
for evaluation as an array formaula, thus
Evaluate("AVERAGE(IF(A1:A8=""Group 1"",B1:B8))")
would work.

Jerry

"robotman" wrote:

> I have a list of differerent-sized groups and values in a table. I am
> trying to determine the Average and Stdev for each group. The formula
> would need to reference the entire data range since the number of data
> points in each group is continually changes.
>
> For example in Col A1:B8 ....
>
>
> Col A Col B
> Group 1 500
> Group 1 230
> Group 1 102
> Group 1 402
> Group 2 300
> Group 2 222
> Group 3 134
> Group 3 153
>
> On the spreadsheet level, I can do a formula like:
>
> =AVERAGE(IF(A1:A8="Group 1",B1:B8))
> (entered as an array formula)
>
> Is there a way to do a similar one-liner in VBA to get the same
> results? I can't figure out how to enter an "array" type formula in
> VBA.
>
> Thanks.
>
>
> John
>
>