AVERAGE / STD of groups in a table?
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 |
AVERAGE / STD of groups in a table?
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 |
All times are GMT +1. The time now is 04:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com