PDA

View Full Version : average calculation



lior03
11-16-2005, 07:11 AM
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

Marcster
11-16-2005, 07:48 AM
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

Surrounded the code in VBA tags for easier reading.

tkaplan
11-16-2005, 07:54 AM
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
11-16-2005, 08:56 AM
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


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

johnske
11-16-2005, 02:13 PM
I think that you want


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
I think Rng.Cells.Count will give an erroneous result, tryFunction 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