Finding average of a range with macro

[ravinder_tig]

Hi Guys
I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application
i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average
dosen't contains all integers
eg if range is A1:A10
then data is like
79
80
98
TBA
98
TBA
TBA
N/A
N/A
N/A
now ave for this range can't be calculated directly as many values are strings
what i need is
using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value
here's da avg dunction which i had used in macro

myColLett = Split(Columns(myCol).Address(False, False), ":")(0)
myFormula = "average(" & "'" & sourceSheetName & "'" & "!" _
                       & myColLett & startRow & ":" _
                       & myColLett & endRow & ")"
               
Where Start row and endrow are elements of a range which do find 1st and last row and my collett finds the corrosponding column alphabet

Your help would be gr8ly appriciated
i'd be really gr8ful for your help
Thanks n Regards,
Ravinder S

[ravinder_tig]

i know avg function do only take integers but what i want is this avg should be divided by total number of integers+total no of tba's

[MaximS]

try that code:

[VBA]
Option Explicit
Sub Avg()
Dim ArrayA() As Variant
Dim i, j, Lr As Long
Dim x As String

'Change A to column where Average will be calculated
Lr = Range("A" & Rows.Count).End(xlUp).Row

j = 0
For i = 2 To Lr
'Change 1 to column number where Average will be calculated
x = Cells(i, 1).Value
If x <> "N/A" Then
ReDim Preserve ArrayA(0 To j)
If x <> "TBA" Then
ArrayA(j) = CLng(x)
j = j + 1
Else
ReDim Preserve ArrayA(0 To j)
ArrayA(j) = 0
j = j + 1
End If
End If
Next i
j = WorksheetFunction.Average(ArrayA())
End Sub
[/VBA]

[GTO]

i know avg function do only take integers but what i want is this avg should be divided by total number of integers+total no of tba's

I'd preface this with I'm not much at formulas, but I used the Average function on your sheet with the vals in B Col and it doesn't seem to need Integers.

That said, Maxim is probably on track but here is what I came up with...

In a Standard module:

[vba]
Option Explicit
Sub ex()
Dim rngRange As Range
Dim rCell As Range
Dim i As Long
Dim dblVal As Double
Set rngRange = Sheet1.Range("B2:B20")

i = 0: dblVal = 0

For Each rCell In rngRange
If IsNumeric(rCell.Value) _
Or UCase(rCell.Value) Like "*TBA*" Then
i = i + 1
If IsNumeric(rCell.Value) Then dblVal = dblVal + rCell.Value
End If
Next

dblVal = dblVal / i
Sheet1.[G1].Value = dblVal
End Sub
[/vba]

Mark

Edit: I forgot to mention, you have spaces around some of the "TBA"s.