macro for average

[aminexcel]

i have this table:
<table style="border-collapse: collapse; width: 243pt;" border="0" cellpadding="0" cellspacing="0" width="321"><col style="width: 26pt;" width="34"> <col style="width: 31pt;" span="7" width="41"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl71" style="height: 11.25pt; width: 26pt;" height="15" width="34">ISO3</td> <td class="xl72" style="border-left: medium none; width: 31pt;" width="41">1999</td> <td class="xl72" style="border-left: medium none; width: 31pt;" width="41">2000</td> <td class="xl72" style="border-left: medium none; width: 31pt;" width="41">2001</td> <td class="xl72" style="border-left: medium none; width: 31pt;" width="41">2002</td> <td class="xl72" style="border-left: medium none; width: 31pt;" width="41">2003</td> <td class="xl72" style="border-left: medium none; width: 31pt;" width="41">2004</td> <td class="xl72" style="border-left: medium none; width: 31pt;" width="41">2005</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">AUS</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">104</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">96</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">113</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">122</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">126</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">AUT</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">104</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">BEL</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">109</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">109</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">CZE</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">98</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">119</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">117</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">118</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">125</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">DNK</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">104</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">108</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">109</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">108</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">FIN</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">FRA</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">108</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">108</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">DEU</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">108</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">GRC</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">104</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">111</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">113</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">114</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">HUN</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">99</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">119</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">122</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">130</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">133</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">ISL</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">97</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">88</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">93</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">99</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">115</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">IRL</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">109</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">120</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">123</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">123</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">ITA</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">104</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">110</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">112</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">111</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">JPN</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">95</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">89</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">83</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">84</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">85</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">79</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">LUX</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">NLD</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">113</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">114</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">113</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">NZL</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">110</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">98</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">116</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">108</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">114</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">NOR</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">112</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">111</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">111</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">POL</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">91</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">113</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">108</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">96</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">96</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">107</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">PRT</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">110</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">111</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">111</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">ESP</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">104</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">109</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">111</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">113</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">SWE</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">92</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">94</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">97</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">CHE</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">104</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">GBR</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">97</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">98</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">99</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">96</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">101</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl73" style="border-top: medium none; height: 11.25pt;" height="15">USA</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">97</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">100</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">106</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">105</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">99</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">94</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">93</td> </tr> </tbody></table>
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

 

[MickG]

Hi, Try this:-
Average of selected line placed in Column "8" average also shown in Msgbox.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Av As Single
If Target.Value <> "" Then
If Intersect(Target, Columns("A")) Is Nothing Or Target.Count > 1 Then Exit Sub
    Av = Application.Average(Target.Offset(, 1).Resize(, 7))
      Target.Offset(, 8) = Av
        MsgBox Av
End If
End Sub

Regards Mick

 

[aminexcel]

thanks a lot MicG
but i want average of column be in last row of each column, for example if i choose AUS, AUT, POL and ITA, it compute average of selected rows in the last row,
thank you very much

 

[MickG]

Hi, Still not sure exactly what you want, but try this:-
If you select your Countries in column "A" by holding down "Ctrl" to Multi select, then run the code the Results Of the average for each column (Year) of the countries Selected will show in the relevant column, at the bottom of the list.

Private Sub CommandButton1_Click()
Dim Last As Integer, rng As Range, cl As Range, col As Integer
Dim sum As Single

Last = Range("A" & Rows.Count).End(xlUp).Row + 1
If Intersect(Selection, Columns("A")) Is Nothing Then Exit Sub
    Set rng = Selection
        For col = 1 To 7
            For Each cl In rng
                sum = sum + cl.Offset(, col)
            Next cl
                Cells(Last, col + 1) = sum / Selection.Count
                sum = 0
         Next col
End Sub

Regards Mick

 

[aminexcel]

thank a lot MicG
but can you do that for worksheet code
i want to do that automatically because i want to change countries for finding my average many times.


cheers

 

[MickG]

Hi, Still not sure exactly how you want this code to work, could you give an example of how you expect to enter the data, and how and where you expect the results, for your numerous searches
For example you could have the countries in a list box with a "Change event " code , that on multi selection will paste details at bottom of sheet, and keep adding details to the list after each multi selection. You could at the same time paste the countries selected in a single cell column "A", to show your selection.
Please Confirm
Regards Mick