# How can I create an Excel PivotTable to calculate the net position of trades?

Data worksheet tracking trades from one person to another:

```+------+----+-----+
| From | To | Amt |
+------+----+-----+
| A    | B  | 10  |
+------+----+-----+
| B    | C  | 5   |
+------+----+-----+
| A    | C  | 3   |
+------+----+-----+
| C    | B  | 2   |
+------+----+-----+
```

How do I create a pivot table (using Calculated Items, Calculated Fields, etc.) that would look like the following?

```+--------+-----+
| Person | Net |
+--------+-----+
| A      | -13 |
+--------+-----+
| B      |   7 |
+--------+-----+
| C      |   6 |
+--------+-----+
| Total  |   0 |
+--------+-----+
```
-

This solution is based on using a converted source table of data.

Assuming your data is in a clean worksheet and your table of data starting from A1:

Type these labels and functions:

E1: `Person`

F1: `Net`

``````E2: =INDEX(A:B,IF(ROW(E2)>COUNTA(A:A),ROW(E2)-COUNTA(A:A)+1,ROW(E2)),IF(ROW(E2)>COUNTA(A:A),2,1))

F2: =INDEX(C:C,IF(ROW(E2)>COUNTA(A:A),ROW(E2)-COUNTA(A:A)+1,ROW(E2)))*IF(ROW(E2)>COUNTA(A:A),1,-1)
``````

then drag to fill with these two functions down, until you see lots of zeros.

Start a Pivot Table with source data range: `E:F` and target positioning within the same worksheet at `H1`.

Drag the `Person` field to the `Row Labels` area, and the `Net` field into the `∑ Values` area and change its summarization function into `Sum`.

Filter the zeros and blanks, and this is it.

I hope it helps.

-