Cosine similarity

[Waimea]

I am currently using CORREL and I would like to be able to calculate "cosine similarity". I have searched but not found anything on this message board.

https://en.wikipedia.org/wiki/Cosine_similarity

 

[joeu2004]

Why would you expect to find anything on this "message board" about this?!

I know nothing about this. But I googled "cosine similarity example" (without quotes) and found a reference to an interesting discussion at https://stackoverflow.com/questions...sine-similarity-in-a-very-simple-graphical-wa. The discussion includes a link to http://www.miislita.com/information-retrieval-tutorial/cosine-similarity-tutorial.html#Cosim.

Combined with your wiki URL, I put together the following demonstration.

Suppose A1:A3 contains 1, 2, 3, and B1:B3 contains 4, -5, 6.

The following computes cos(theta) in C1, according to the wikipage:

=SUMPRODUCT(A1:A3, B1:B3) / SQRT(SUMSQ(A1:A3)) / SQRT(SUMSQ(B1:B3))

(Note that x/(y*z) = x/y/z.)

The result in C1 is 0.365486942323904. That rounds to 0.37, which agrees with the tutorial.

Recall that in Excel, the parameter of COS is in radians.

So "theta" (angle in degrees) is:

=DEGREES(ACOS(C1))

The result is 68.5624469663971, which rounds to 68.6 degrees.

 

[Waimea]

Thank you for your reply joeu2004! Your answer is very good and I am reading the tutorial right now.

Can you help me to create a UDF for cosine similarity?

Sub CosineSimialarity(array1, array2)


    Application.WorksheetFunction.SumProduct(array1, array2) = "application.WorksheetFunction.Sqrt() = """ / Application.WorksheetFunction.SumSq = "array1" / Application.WorksheetFunction.Sqrt() = "Application.WorksheetFunction.SumSq = "array1")


    Application.WorksheetFunction.Degrees = "Application.WorksheetFunction.Acos = "" "


End Sub

All the worksheet functions are in the UDF but I am not sure what parameters to pass into the function?

 

[Waimea]

=SQRT(SUMXMY2(C3:F3,C11:F11))

Could anyone explain this formula? From https://stackoverflow.com/questions/36509664/excel-formula-for-euclidean-distance?rq=1

 

[Waimea]

I am still trying to create a UDF from the code in the second post. Can anyone help?

 

[Kenneth Hobson]

If you want to use VBA, Evaluate() is probably best for post #2 . e.g.

Sub Test_CosSim()
  [A1] = 1: [A2] = 2: [A3] = 3
  [B1] = 4: [B2] = -5: [B3] = 6
  MsgBox CosSim([A1:A3], [B1:B3])
End Sub

'=cossim(A1:A3,B1:B3)
Function CosSim(r1 As Range, r2 As Range) As Double
  Dim s1 As String, s2 As String
  s1 = r1.Address
  s2 = r2.Address
  ''=SUMPRODUCT(A1:A3, B1:B3) / SQRT(SUMSQ(A1:A3)) / SQRT(SUMSQ(B1:B3))
  CosSim = Evaluate("Degrees(ACos(Sumproduct(" & s1 & "," & s2 & ")/Sqrt(sumsq(" & s1 & _
    "))/Sqrt(sumsq(" & s2 & "))))")
End Function

 

[Waimea]

Hi Kenneth Hobson,

thank you very much for your reply and for your code. I am going to try it out at once and I am definitely going to google Evaluate().

 

[Kenneth Hobson]

No need to google a command word. In VBE, F2 will let you look around. With cursor in or next to a command word, F1 will open the specific help.

 

[joeu2004]

If you want to use VBA, Evaluate() is probably best for post #2

Not at all. VBA Evaluate does all the real work in the Excel thread. Very inefficient. Simply use WorksheetFunction.

Function cosSim(a As Variant, b As Variant) As Variant
Dim wf As Object
Set wf = WorksheetFunction
On Error Resume Next
cosSim = wf.SumProduct(a, b) / Sqr(wf.SumSq(a)) / Sqr(wf.SumSq(b))
If Err <> 0 Then cosSim = CVErr(xlErrValue)
End Function

Be sure to put the function into a normal VBA module; that is, in VBA, click Insert > Module.

Examples of usage:
=cosSim({1,2,3}, {4,-5,6})
=cosSim(A1:A3, B1:B3)

I did not respond earlier because I cannot see any reason to implement this in VBA, unless you need to call cosSim() from existing VBA code. Otherwise, it is much more efficient to use the Excel formula that I provided earlier.

 

[shg]

Or skip all of Excel:

Function CosSimS(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Variant
  Dim dMag As Double
  Const D2R = 180# / 3.141592654

  dMag = (x1 * x1 + y1 * y1) * (x2 * x2 + y2 * y2)
  If dMag Then
    CosSimS = (x1 * x2 + y1 * y2) / Sqr(dMag) * D2R
  Else
    CosSimS = CVErr(xlErrDiv0)
  End If
End Function