Statistical
Cosine Similarity and Euclidean Distance between two vectors in cells
Compare embeddings, customer preference profiles, or document term vectors using SUMPRODUCT dot products and L2 vector norms.
Direction (Cosine Similarity) vs Magnitude Gap (Euclidean L2 Distance)
Recommendation engines, SEO embedding audits, and clustering models compare two numeric feature vectors A and B in two complementary ways: (1) Cosine Similarity = (A · B) / (||A|| × ||B||) ranges from -1 (opposite) through 0 (orthogonal) to +1 (identical direction, regardless of scale!), and (2) Euclidean L2 Distance = SQRT(SUM((A - B)^2)) measures straight-line geometric distance.
Watch the difference on Shopper A = [3, 4, 0] vs Heavy Shopper B = [6, 8, 0] (who buys the exact same category mix in 2× quantities): Euclidean Distance = SQRT((3-6)^2 + (4-8)^2) = 5.0000, yet Cosine Similarity = (18 + 32) / (5 × 10) = 50 / 50 = 1.0000 because the numerical vectors point in the same direction.
Match the dimensions and feature ordering and use numeric inputs. Euclidean distance depends on feature units and scaling. Orthogonality does not prove semantic unrelatedness; the interpretation depends on the chosen representation. Cosine similarity is undefined for a zero vector.
| Vector Pair (A vs B) | Dot Product A·B | Cosine Similarity [-1, +1] | Euclidean Distance |
|---|---|---|---|
| [3, 4, 0] vs [6, 8, 0] (2× scale) | 50 | +1.0000 (Identical mix!) | 5.0000 |
| [3, 4, 0] vs [-4, 3, 0] (90°) | 0 | 0.0000 (Orthogonal) | 7.0711 (SQRT(50)) |
| [3, 4, 0] vs [-3, -4, 0] (180°) | -25 | -1.0000 (Exact opposite) | 10.0000 |
Cosine Similarity and Euclidean L2 Distance formulas
SUMPRODUCT(A2:A4, B2:B4) computes the numerator dot product, while SQRT(SUM(A2:A4 ^ 2)) * SQRT(SUM(B2:B4 ^ 2)) multiplies the two L2 norms.
=IF(OR(COUNT(A2:A4)<>3,COUNT(B2:B4)<>3),"Enter numeric vectors",IF(OR(SUM(A2:A4^2)=0,SUM(B2:B4^2)=0),"Undefined for zero vector",ROUND(SUMPRODUCT(A2:A4, B2:B4) / (SQRT(SUM(A2:A4 ^ 2)) * SQRT(SUM(B2:B4 ^ 2))), 4)))Euclidean L2 straight-line distance between two vectors
Array subtraction (A2:A4 - B2:B4) ^ 2 works for any dimensionality (3D coordinates or 100-row embeddings).
=ROUND(SQRT(SUM((A2:A4 - B2:B4) ^ 2)), 4)Try the example
Use the original values or change them to test your understanding.