UDF to calculate average

[froswick]

Hi, my first post and very new to VBA so any help appreciated. I'm trying to create a function to calculate the average of a range of cells in a column. The first cell is always the cell to the left of the cell where the UDF will be used, the last cell in the range will depend on a parameter passed to the function ie if 3 is passed, the range will be three cells deep etc
I thought this would work but no joy

Function wkavg(hours As Single)
ActiveCell.FormulaR1C1 = "=SUM(RC[-1}:R[" & hours & "]C[-1])"
End Function


Any ideas please? Thanks.

 

[gauntletxg]

You don't need a UDF.

Excel Workbook
	A	B
1	100	200
2	200	*
3	300	*
4	400	*
5	500	*
Sheet1

3 is your factor

edit: if you really wanted a UDF

Function MyAverage(rngReference As Range, factor As Integer) As Variant
MyAverage = Evaluate("AVERAGE(OFFSET(" & rngReference.Address & ",,," & factor & "))")
End Function

 

[froswick]

Thanks very much, that's just what I need, you're right, I don't need a UDF it seems. Thanks for your help, much appreciated..

 

[Evan Galvanek]

Thanks for the tips in this thread. I used what I read here to create a PPM calculator.

Function PPM(experimental As Range, Theoretical As Range) As Variant
Dim countfactor As Integer
Dim avgExp As Double
Dim avgThe As Double
countfactor = experimental.Count
avgExp = Evaluate("AVERAGE(OFFSET(" & experimental.Address & ",,," & countfactor & "))")
avgThe = Evaluate("AVERAGE(OFFSET(" & Theoretical.Address & ",,," & countfactor & "))")
PPM = 1000000 * ((avgExp - avgThe) / avgThe)
End Function