PDA

View Full Version : Sleeper: Summing & average by numberformat



lior03
06-28-2005, 12:30 AM
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
06-28-2005, 02:47 AM
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
06-28-2005, 05:51 AM
You could also consider using the line

If cell.NumberFormat = ActiveCell.NumberFormat Then
which could give you some added flexibility.

Zack Barresse
06-28-2005, 08:07 AM
Re: http://www.vbaexpress.com/forum/showthread.php?t=3770

Please stick to one thread. Thanks. :)