The Difference Function

Available in Sets.xla, a Microsoft© Excel add-in.
Click "Difference",or scroll this page for Difference examples.
Click on another function in the browser window to read about it.
Click a function.

Difference Right Associative Difference Disjoint Union Intersection MakeList MakeSet MatrixToList NMakeList PowerSet SymmetricDifference Union

The Difference Function

The Difference function returns a set from a range of cells.
The range of cells is called the argument of the function.
The Difference function can be called in one of two ways.
  1. From the function browser, above.
  2. Typing the function directly into a cell.

List Examples

In these examples, the contents of each cell in the argument is a simple list.

Two-Part Example
A1 B1 =Difference(A1:B1)
1,2,3,4,5 1,2,3 {4,5}


Three-Part Example
Cell Contents
A1 1,2,3,4,5
A2 1,2,3
A3 4
=Difference(A1:A3) {5}

Here is What is Going On

The 1, the 2 and the 3 get taken away from 1,2,3,4,5 because they occur
in cell A2. The 4 gets taken away from cell A1 because it occurs in cell A3

Set Examples

In these examples, each cell in the argument is treated as a set.
The contents of each cell are treated as elements.

Two-Part Examples
Example A1 B1 =Difference(A1:B1)
1 {1,2,3,4,5} {1,2,3} {{1,2,3,4,5}}
2 {1,2,3,4,5},1 {1,2,3} {{1,2,3,4,5},1}
3 {1,2,3,4,5},1 {1,2,3},1 {{1,2,3,4,5}}


Three-Part Example
Cell Contents
A1 {1,2,3,4,5},{1,2,3},1
A2 {1,2,3}
A3 {1}
=Difference(A1:A3) {{1,2,3,4,5},1}

Rules for Selections

In the two-part and three-part examples, cells selected for differencing are contiguous.
Noncontigous cells may be differenced by reference to a workbook variable.
Cells A1 and A3 are assigned the workbook variable name, "WVName".

Workbook Variable Name Example
Cell Contents
A1 1,2,3,4,5
A2 1,2,3
A3 4
=Difference(WVName) {1,2,3,5}
=Difference(A1,A3) #VALUE!

If the cells are referenced directly, a #VALUE results.

Created on ... December 27, 2001