Loop through Range/Array - Vectorized Calculations (VBA UDF)

[drpdrpdrp]

I understand that I can take a range of values and make a vectorized calculation on it by using {...} in plain excel (Method 2)
(Method 1) below is showing the simplest way of accomplishing what I need:

1. Take the values in a range/vector/array,
2. subtract a fixed value from each
3. save as a new vector/range/array
4. average the values in the new_vector, and return the single value result

How can I write a VBA UDF { MyFunction(range, val) } to use on G9 (Method3) ?

Book1
	A	B	C	D	E	F	G	H
1	rng				Method1	Method2	Method3
2	12	val:	10		"manual"	"array"	VBA
3	23				2
4	54				13
5	65				44
6	12				55
7	345				2
8					335
9					75.2	75.2	???
10
Sheet1

Cell Formulas
Range	Formula
E3:E8	E3	=A2-$C$2
E9	E9	=AVERAGE(E3:E8)
F9	F9	=AVERAGE($A$2:$A$7-$C$2)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Function drpdrp(Rng As Range, Numb As Double) As Double
   drpdrp = Evaluate("average(" & Rng.Address & "-" & Numb & ")")
End Function

 

[drpdrpdrp]

Thanks for the warming welcoming and reply, @Fluff.

Your proposed solution works great. Since we are on the topic: what would be a general solution with a For Loop that is easier to understand and extend later ?

 

[Fluff]

How about

Function drpdrp(Rng As Range, Numb As Double) As Double
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Rng.Value
   For i = 1 To UBound(Ary)
      Ary(i, 1) = Ary(i, 1) - Numb
   Next i
   drpdrp = Application.Average(Ary)
End Function

 

[drpdrpdrp]

How about

Function drpdrp(Rng As Range, Numb As Double) As Double
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Rng.Value
   For i = 1 To UBound(Ary)
      Ary(i, 1) = Ary(i, 1) - Numb
   Next i
   drpdrp = Application.Average(Ary)
End Function

Much appreciated, Fluff. I was struggling with the Ary(x,y) part.... not obvious to me why the 2nd dimension has to be taken into account if it´s a 1-D Array.
Just need to get used to VBA. How do I mark your answer as the correct/selected one ?

 

[Fluff]

It's not a 1d array, whenever you load an array from a range it will be a 2d array, regardless of the number of rows or columns.

How do I mark your answer as the correct/selected one ?

Just click the Tick symbol to the right of the relevant post.