Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weighted avg. of groups in table | Excel Programming | |||
Pivot Table Groups | Excel Worksheet Functions | |||
pivot table and groups | Excel Worksheet Functions | |||
average and stdev from groups of data | Excel Worksheet Functions | |||
MAX, MIN, MEAN, AVERAGE, MODE for multiple groups...how? | Excel Worksheet Functions |