Looking for a UDF to calculate for a practical question

[alice2011]

Dear all, I have a practical question to solve using a UDF. I have little knowledge about VBA, so really look for your help.

The question is: to calculate the percentage of years that a company had positive dividend growth. So the UDF will be something like: DivGrowth(Range), here the range gives the history of annual dividends paid by a company over the past years.

Please see the example attached here.

Column A is the year, and column B is the dividend paid by the company for that year. If the value is not numeric (n.a.), then it means no dividend paid. But to make it simple, only count from the first cell with numeric value in the range (cell B5 in the example). To get the percentage of years that a company had positive dividend growth, I just specify it as: DivGrowth(B1:B20). Column C indicates if there is a dividend growth: 1 means dividend is better than last year, 0 otherwise. The expected result is shown in cell F5.

I hope the function can be flexible. For example, if there are less than 5 numeric data points in the range specified, then just do not calculate and simply return a "-" or a blank space "". Also, ignore the first few cells which all have "n.a." values: just count from the first valid numeric data point (cell B5 in that example).

[Leith Ross]

Hello alice2011,

The attached workbook has the UDF shown below added to it. The UDF will ignore rnages with less than 5 cells and simply return a blank. The UDF scan the range to find the first numeric value in the range. Any non numeric values after that point are counted by summed as a zero value. Check this over a let me know if I missed anything.

[b}UDF Macro Code[/b]

Please Login or Register  to view this content.

[alice2011]

Hi, Leith Ross, your code works perfectly. Really nice

A big thank you to you.