macro to calculate average on varying sized rows with missing valu

[intoit]

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+q5_dif_data)/5,"")"

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

 

[Gary''s Student]

The first thing to change is from:
Cells(role_average, 162) =
to:
Cells(role_average, 162).Formula =

 

[Joel]

You may wantt to use a UDF macro instead of a sub.



Sub RollAverage()


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, "DM").End(xlUp).Row
q2_dif_data = .Cells(65536, "DN").End(xlUp).Row
q3_dif_data = .Cells(65536, "DO").End(xlUp).Row
q4_dif_data = .Cells(65536, "DP").End(xlUp).Row
q5_dif_data = .Cells(65536, "DQ").End(xlUp).Row

Mytotal = q1_dif_data + q2_dif_data + q3_dif_data + q4_dif_data +
q5_dif_data
If Mytotal = 5 Then
.Cells(role_average, "FF") = Mytotal / 5
End If

.Range("FF1") = "Role -- Average"

End With
End Sub

 

[Barb Reinhardt]

Try this

Option Explicit

Sub Test()
Dim role_average As Long 'Integer
Dim myWS As Excel.Worksheet
Dim myWB As Excel.Workbook
Dim myRange As Excel.Range
Dim lRow As Long

'role_average = 2 'Added so code would work

Set myWB = ThisWorkbook 'Get used to referencing to workbook
Set myWS = myWB.Worksheets("Data")

lRow = myWS.Rows(myWS.Rows.Count).End(xlUp).Row
Set myRange = myWS.Cells(lRow, 117).Resize(1, 5)

myWS.Cells(role_average, 162).FormulaArray = _
"=Average(IF(ISNUMBER(" & myRange.Address & "), " & myRange.Address & "))"

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


Keep in mind that if/when you go to Excel 2007, anything that references a
row will need to be dimensioned as Long instead of Integer. Also, if you
hard code the last row in Excel 2003, it'll change in Excel 2007 and you'll
have to change 'em all.

From Help

Integer variables are stored as 16-bit (2-byte) numbers ranging in value
from -32,768 to 32,767.

If you ever get to the last row, you won't be able to use the Integer data
type now anyway.

HTH,
Barb Reinhardt

 

[intoit]

Thanks for the responses, Gary, Joel, and Barb. I do appreciate it.

Joel: I tried running your code, but it gives an error, as Mytotal was not
defined as a Dim. However, even when I define Dim Mytotal As Integer, it does
not produce any calculations on the Data Worksheet (it does, however, produce
the 'Role -- Average' text in FF1). It's almost as if the macro determined
that none of the rows satisfied the condition that Mytotal = 5 (?).

Barb: Thanks for the code and general macro writing tips. Your suggested
code runs through without error, however, it only produces a 'result' for the
first row and it is equal to a '#DIV/0!'

If it is any help, this following macro does calculate averages for the five
columns of data, but it stops (or runs into an error) as soon as it hits a
'#VALUE!' within the rows of data from which the averages are to be
calculated. It also makes the 'mistake' of calculating an average for a case
that does not have a complete set of data across all five rows.

Thanks for any help. I really do appreciate it.

Sub my_test2()

Dim role_average As Long

Dim q1_dif_data As Long
Dim q2_dif_data As Long
Dim q3_dif_data As Long
Dim q4_dif_data As Long
Dim q5_dif_data As Long


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


For role_average = 1 To Application.WorksheetFunction.Max(q1_dif_data,
q2_dif_data, q3_dif_data, q4_dif_data, q5_dif_data)
If .Cells(role_average, 117) & .Cells(role_average, 118) &
..Cells(role_average, 119) & .Cells(role_average, 120) & .Cells(role_average,
121) <> "" Then _
.Cells(role_average, 162) = "=AVERAGE(RC[-45]:RC[-31])"
Next role_average
End With
Range("Data!FF1") = "Role -- Average"
End Sub