Sleeper: Summing & average by numberformat

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

Quote:

---

Originally Posted by 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

---

Code:

---

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

---

You could also consider using the line

Code:

---

If cell.NumberFormat = ActiveCell.NumberFormat Then

---

which could give you some added flexibility.

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

Please stick to one thread. Thanks. :)