I see the problem now.
If you have 4 cells containing 2,5,nvt,blank, what do you want the
average to be?
Chip's gives 3.5, both my snippets give 2.333. This is because my
formula treats blank cells as a zero. Probably not what you want.

Do you want to exclude both strings and empty cells from the averaging
altogether?

Using Evaluate in Chip's code will still cause an error so I suggest
using an adaptation of his code:


Set R = Range("FirstCell")
R.CurrentRegion.Sort R, xlAscending
Set R2 = R
Do Until R.Value = vbNullString
If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
N = N + 1
Else
xxx = Empty
On Error Resume Next
xxx = Application.WorksheetFunction.Average(R2(1, 3).Resize(N + 1,
1))
On Error GoTo 0
R2(1, 7).Resize(N + 1, 1) = "Average of '" & R.Text &
IIf(IsEmpty(xxx), "' = Oops!", "' = " & xxx)
N = 0
Set R2 = R(2, 1)
End If
Set R = R(2, 1)
Loop
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176334

Microsoft Office Help