Semipartial Correlation UDF

Juggler_IN

Active Member
Joined
Nov 19, 2014
Messages
358
Office Version
  1. 2003 or older
Platform
  1. Windows
I need help in modifying the appended Partial Correlation UDF to Semipartial Correlation UDF.

A note on Semipartial Correlation (and Partial) can be found at http://www.listendata.com/2017/03/partial-correlation.html

The Partial Correlation UDF is:

Code:
Function pCorrel(R)
Dim iRows As Integer, iCols As Integer
Dim Identity() As Double, RDiag() As Double, RDiagSQRT() As Double, Part_Correl() As Double
iRows = R.Rows.Count
iCols = R.Columns.Count
RInverse = Application.MInverse(R)
ReDim Identity(1 To iRows, 1 To iCols)
ReDim RDiag(1 To iRows, 1 To iCols)
ReDim RDiagSQRT(1 To iRows, 1 To iCols)
ReDim Part_Correl(1 To iRows, 1 To iCols)
For i = 1 To iRows
For j = 1 To iCols
Identity(i, j) = 0
RDiag(i, j) = 0
If i = j Then
Identity(i, j) = 1
RDiag(i, j) = 1 / RInverse(i, j)
RDiagSQRT(i, j) = RDiag(i, j) ^ 0.5
End If
Next j
Next i
P2_Neg = (Application.MMult(RDiagSQRT, Application.MMult(RInverse, RDiagSQRT)))
For i = 1 To iRows
For j = 1 To iCols
Part_Correl(i, j) = Identity(i, j) - P2_Neg(i, j)
Part_Correl(i, i) = -1
Next j
Next i
pCorrel = Part_Correl
End Function
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Further to add an example, if the correlation matrix for three variables is:

[TABLE="width: 270"]
<tbody>[TR]
[TD="align: right"]1.000000
[/TD]
[TD="align: right"]0.920000
[/TD]
[TD="align: right"]0.870000
[/TD]
[/TR]
[TR]
[TD="align: right"]0.920000
[/TD]
[TD="align: right"]1.000000
[/TD]
[TD="align: right"]0.810000
[/TD]
[/TR]
[TR]
[TD="align: right"]0.870000
[/TD]
[TD="align: right"]0.810000
[/TD]
[TD="align: right"]1.000000
[/TD]
[/TR]
</tbody>[/TABLE]

... the array function pCorrel() gives the following output.

[TABLE="width: 225"]
<tbody>[TR]
[TD="align: right"]-1.00000
[/TD]
[TD="align: right"]0.744621
[/TD]
[TD="align: right"]0.543004
[/TD]
[/TR]
[TR]
[TD="align: right"]0.744621
[/TD]
[TD="align: right"]-1.00000
[/TD]
[TD="align: right"]0.049680
[/TD]
[/TR]
[TR]
[TD="align: right"]0.543004
[/TD]
[TD="align: right"]0.049680
[/TD]
[TD="align: right"]-1.00000
[/TD]
[/TR]
</tbody>[/TABLE]

.. Now, instead of Partial, I am looking for Semipartial UDF.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top