average calculation

[lior03]

hello
the aim of the following function is to calculate the average of a specific
number of columns containing only a required number format.
why is it not working
[VBA]
Function SumByFormat10(Rng As Range, Optional fmt As String = "0.00%")
Dim cell As Range, amt As Double, i As Integer
For Each cell In Rng
i = Rng.Columns.count
If cell.numberformat = fmt Then
If IsNumeric(cell.Value) Then
SumByFormat10 = (SumByFormat10 + cell.Value) / i
End If
End If
Next cell
End Function
[/VBA]

[Marcster]

[VBA]Function SumByFormat10(Rng As Range, Optional fmt As String = "0.00%")
Dim cell As Range, amt As Double, i As Integer
For Each cell In Rng
i = Rng.Columns.count
If cell.numberformat = fmt Then
If IsNumeric(cell.Value) Then
SumByFormat10 = (SumByFormat10 + cell.Value) / i
End If
End If
Next cell
End Function[/VBA]

Surrounded the code in VBA tags for easier reading.

[tkaplan]

it worked for me.
make sure you put the format of the range of cells as the same format that you put into the fmt parameter.

btw, me'eifoh ata ba'aretz?

[Bob Phillips]

hello
the aim of the following function is to calculate the average of a specific
number of columns containing only a required number format.
why is it not working
Function SumByFormat10(Rng As Range, Optional fmt As String = "0.00%")
Dim cell As Range, amt As Double, i As Integer
For Each cell In Rng
i = Rng.Columns.count
If cell.numberformat = fmt Then
If IsNumeric(cell.Value) Then
SumByFormat10 = (SumByFormat10 + cell.Value) / i
End If
End If
Next cell
End Function

I think that you want

[vba]
Function SumByFormat10(Rng As Range, Optional fmt As String = "0.00%")
Dim cell As Range, amt As Double, i As Integer

For Each cell In Rng
If cell.NumberFormat = fmt Then
If IsNumeric(cell.Value) Then
SumByFormat10 = (SumByFormat10 + cell.Value)
End If
End If
Next cell
SumByFormat10 = SumByFormat10 / Rng.Cells.Count
End Function
[/vba]

[johnske]

I think that you want

[vba]
Function SumByFormat10(Rng As Range, Optional fmt As String = "0.00%")
Dim cell As Range, amt As Double, i As Integer

For Each cell In Rng
If cell.NumberFormat = fmt Then
If IsNumeric(cell.Value) Then
SumByFormat10 = (SumByFormat10 + cell.Value)
End If
End If
Next cell
SumByFormat10 = SumByFormat10 / Rng.Cells.Count
End Function
[/vba]

I think Rng.Cells.Count will give an erroneous result, try[vba]Function SumByFormat10(Rng As Range, Optional fmt As String = "0.00%")
Dim cell As Range, N As Long
N = 0
For Each cell In Rng
If cell.NumberFormat = fmt Then
If IsNumeric(cell) Then
SumByFormat10 = (SumByFormat10 + cell)
N = N + 1
End If
End If
Next cell
SumByFormat10 = SumByFormat10 / N
End Function[/vba]