Hi,

I'm using excel 2003. I'd like to calculate the average associated with a
large number of rows across five columns. Note that in some cells there are
missing values or even #VALUE! errors (ie., because the averages are based on
values derived from other calculations). For a single row, the following
formula works very well:

=IF(COUNT(DM2,DN2,DO2,DP2,DQ2)=5, (DM2+DN2+DO2+DP2+DR2)/5,"")

Now, I would like to incorporate this formula into a macro which could be
applied to different data sets, which will have different numbers of
cases/rows. My current macro looks like this, but it doesn't work. I get an
error on the 'Cells(role_average, 162) = ...' line.

Any suggestions would be much appreciated.

Dim role_average As Integer
Dim q1_dif_data As Integer
Dim q2_dif_data As Integer
Dim q3_dif_data As Integer
Dim q4_dif_data As Integer
Dim q5_dif_data As Integer

With Sheets("Data")
q1_dif_data = .Cells(65536, 117).End(xlUp).Row
q2_dif_data = .Cells(65536, 118).End(xlUp).Row
q3_dif_data = .Cells(65536, 119).End(xlUp).Row
q4_dif_data = .Cells(65536, 120).End(xlUp).Row
q5_dif_data = .Cells(65536, 121).End(xlUp).Row

Cells(role_average, 162) =
"=IF(COUNT(q1_dif_data,q2_dif_data,q3_dif_data,q4_ dif_data,q5_dif_data)=5,
(q1_dif_data+q2_dif_data+q3_dif_data+q4_dif_data+q 5_dif_data)/5,"")"

End With
Range("Data!FF1") = "Role -- Average"
End Sub