Sleeper: Summing & average by numberformat

[lior03]

attached is a workbook with the problem.
a database of economic data year by year .each column of data is ccompanied by a column of growth ratio to the year befor formated in "0.00%".
i am trying to build a UDF to deal with average growth rate ,as well as summig the data formated in "##,###.00"
for instance - in sheet gdpf how can i get the average of data formated in "0.00%".
thanks

[Bob Phillips]

attached is a workbook with the problem.
a database of economic data year by year .each column of data is ccompanied by a column of growth ratio to the year befor formated in "0.00%".
i am trying to build a UDF to deal with average growth rate ,as well as summig the data formated in "##,###.00"
for instance - in sheet gdpf how can i get the average of data formated in "0.00%".
thanks

Function SumByFormat(rng As Range, Optional fmt As String = "0.00%")
Dim cell As Range, amt As Double
    For Each cell In rng
        If cell.NumberFormat = fmt Then
            If IsNumeric(cell.Value) Then
                SumByFormat = SumByFormat + cell.Value
            End If
        End If
    Next cell
End Function

[mdmackillop]

You could also consider using the line

If cell.NumberFormat = ActiveCell.NumberFormat Then

which could give you some added flexibility.

[Zack Barresse]

Re: http://www.vbaexpress.com/forum/showthread.php?t=3770

Please stick to one thread. Thanks.