Thread: conditional averaging macro

i have this table:
ISO3 1999 2000 2001 2002 2003 2004 2005
AUS 104 100 96 101 113 122 126
AUT 103 100 100 101 104 106 106
BEL 105 100 101 102 107 109 109
CZE 98 100 106 119 117 118 125
DNK 104 100 101 103 108 109 108
FIN 105 100 100 102 107 107 105
FRA 106 100 100 101 107 108 108
DEU 107 100 100 101 106 108 107
GRC 107 100 101 104 111 113 114
HUN 99 100 107 119 122 130 133
ISL 97 100 88 93 99 102 115
IRL 105 100 103 109 120 123 123
ITA 105 100 101 104 110 112 111
JPN 95 100 89 83 84 85 79
LUX 102 100 101 102 105 107 107
NLD 105 100 103 107 113 114 113
NZL 110 100 98 107 116 108 114
NOR 102 100 103 112 111 107 111
POL 91 100 113 108 96 96 107
PRT 103 100 102 105 110 111 111
ESP 102 100 102 104 109 111 113
SWE 103 100 92 94 100 101 97
CHE 103 100 103 106 106 106 104
GBR 97 100 98 99 96 102 101
USA 97 100 106 105 99 94 93
Average

column one containing ISO3 names of different countries. I was wondering if it's possible to have a macro that when i choosing some countries in different rows, it compute average value of them for any year in seven columns

thanks a lot

A macro isn't any better or faster than a normal AutoFilter list with a "key" column to flag the countries to include. Like so... use the drop down box in the KEY cell and select "1" as the filter. Select "All" to see all countries and change your selections before filtering again.

The averages at the bottom show the average of the visible countries, and the average of all.

Actually, I suppose once you've added the KEY column you don't REALLY need the autofilter, it just makes it really nice visually to use it. Once the KEY column is there, then this formula at the bottom of the chart will show the AVERAGE in realtime of the item's you have flagged:

=SUMIF($I$2:$I$26,1,B2:B26)/COUNTIF($I$2:$I$26,1)

...for column B, copy that across from there.