# question

## calculate the average correlation among stocks whose correlations are between 25% (LB) and 75% (UB).

I need help for ending my code i VBA, I am very new to coding and dont know how to code this.. I got an array with the returns of 5 different stocks over the past 5 years. I need to calculate the average correlation among these stocks whose correlations are between 25% (LB) and 75% (UB). So far my coding looks like this, I've managed to calculate the "return average correlation".

Option Explicit

Function AvgRhoBounded(RET, LB, UB)

`````` ' get number of assets
``````

n = data.Columns.Count

`````` ' add up all returns
``````

total_rho = 0
n_rho = 0
For i = 1 To n
For j = i + 1 To n

``````         rho_ij = Application.WorksheetFunction.Correl(data.Columns(i), data.Columns(j))
total_rho = total_rho + rho_ij
n_rho = n_rho + 1

Next j
Next i

' return average correlations
``````

AvgRho = total_rho / n_rho

'calculating the average correlation between stocks whose correlation are between 25% and 75%

Dim LB As String
Dim Lowerbound As String

Dim RET As String

Dim UB As String
Dim Upperbound As String

If LB = 25 Then
Lowerbound = 25

ElseIf UB = 75 Then
Upperbound = 75

End If

End Function

... I've read that I can use the dim function, the loop function, the if-statment function and the LB/UB function but I dont understand how.

I appreciate any help that i get!!

· 1

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@sabrinasamuelsen-9409
As your issue is about VBA, but the tag "office-excel-itpro" focus on general issues of Excel, I would remove it.