VBA : Function for Simulations

Hi guys,

I have range A1 to A5 : Each containing a function =NORM.INV(RAND(),1,1). So whenever I refresh the values would change.

I have range A6 which contains a sum of range A1 to A5 : =SUM(A1:A5) . Again, whenever I refresh the value would change.

Now, I want to create an excel function such that based on the number specified in the function( say n = 3), the function should be able to get 3 random value of range A6. Then based on that i will be able to find the average of this 3 values.

Its like refreshing 3 times and put them into an array and getting the average.

So at the moment I have something like this:

Function OutputSim1(No As Integer, Val As Double)
    Dim Sim() As Double
    ReDim Sim(No)
    Dim i As Integer
    For i = 1 To No
    Application.Volatile (True)
    Sim(i) = Val
    Sum = Sum + Sim(i)
    Next i
OutputSim1 = Sum / No    

End Function

Really appreciate the help.

Thank You.