A
B
C
D
1
Qty
Price
Delta
2
0
$ 15.00
$ 15.00
C2: =B2 -N(B1)
3
1,000
$ 10.00
$ (5.00)
4
5,000
$ 6.00
$ (4.00)
5
10,000
$ 5.00
$ (1.00)
6
20,000
$ 4.50
$ (0.50)
7
50,000
$ 4.00
$ (0.50)
8
100,000
$ 3.50
$ (0.50)
9
10
Qty
Price
Avg Price
11
100
$ 1,500
$ 15.00
B11: =SUMPRODUCT((A11 > $A$2:$A$8) * (A11 - $A$2:$A$8) * $C$2:$C$8)
12
200
$ 3,000
$ 15.00
13
500
$ 7,500
$ 15.00
14
1,000
$ 15,000
$ 15.00
15
2,000
$ 25,000
$ 12.50
16
5,000
$ 55,000
$ 11.00
17
10,000
$ 85,000
$ 8.50
18
20,000
$ 135,000
$ 6.75
19
50,000
$ 270,000
$ 5.40
20
100,000
$ 470,000
$ 4.70
21
200,000
$ 820,000
$ 4.10
22
500,000
$ 1,870,000
$ 3.74