Formula for Average/Stdev of groups in a table

[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 using a
formula. 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

Can someone tell me a formula to get the AVERAGE and STDEV of a
particular group in range A1:B8?

Thanks.

John

 

[Jerry W. Lewis]

If I understand your question, then you want an array formula like
=AVERAGE(IF(A1:A8="Group 1",B1:B8))
Array formulas must be array entered (Ctrl+Shift+Enter) to work.

Jerry

 

[robotman]

Thanks for the formula. I forgot to mention that I'm actually trying
to do the calculation in VBA so it needs to be a formula I can
translate into VBA. Is there a way to do "array formulas" in VBA?

I'll repost in the programming forum to see if they have any ideas...

Thanks again.

John