AVERAGE / STD of groups in a table?

R

robotman

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
 
J

Jerry W. Lewis

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top