Cosine similarity

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,800
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372
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?



Code:
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

Active Member
Joined
Jun 30, 2018
Messages
372

ADVERTISEMENT

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

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
If you want to use VBA, Evaluate() is probably best for post #2 . e.g.
Code:
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
 
Last edited:

Waimea

Active Member
Joined
Jun 30, 2018
Messages
372

ADVERTISEMENT

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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,800
Office Version
  1. 2010
Platform
  1. Windows
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.

Rich (BB code):
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.
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Or skip all of Excel:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,729
Messages
5,524,488
Members
409,584
Latest member
RedHelp

This Week's Hot Topics

Top