# Cosine similarity

### 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

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

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

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

#### Kenneth Hobson

##### Well-known Member
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
''=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

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
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
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
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``````

Replies
3
Views
63
Replies
1
Views
231
Replies
2
Views
74
Replies
5
Views
115
Replies
24
Views
109