I need help trying to figure out how to get calculate the average price of ZS every time the quantity column reaches a multiple of 10 (running total) for corresponding ZS rows only. In this example calculate average price of ZS at I9,I12,I17, I20(only the first a quantity of 2 from that price so that it will complete the multiple of 10), I23. I would only like the calculation to occur if it is not going to be the same value as the calculation the previous quantity of 10. (Only focus on the "Real price" not the "Price" because the software that gives out the summary that paste into this template shortcuts prices for ZS so i made a formula to change that back.) Thanks in advance!
Excel 2012 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | |||
8 | Contract | Price | BuySell | Quantity | Real Price | Crush @ fill | ||||
9 | ZS | 10652 | Buy | 10 | 10652.5 | 122.8 | ||||
10 | ZL | 3124 | Sell | 9 | 3124 | |||||
11 | ZM | 3838 | Sell | 11 | 3838 | |||||
12 | ZS | 10680 | Buy | 10 | 10680 | Calculate | ||||
13 | ZL | 3124 | Sell | 9 | 3124 | |||||
14 | ZM | 3838 | Sell | 11 | 3838 | |||||
15 | ZS | 10676 | Buy | 1 | 10677.5 | |||||
16 | ZS | 10680 | Buy | 7 | 10680 | |||||
17 | ZS | 10682 | Buy | 12 | 10682.5 | calculate | ||||
18 | ZL | 3124 | Sell | 9 | 3124 | |||||
19 | ZM | 3838 | Sell | 11 | 3838 | |||||
20 | ZS | 10692 | Buy | 10 | 10692.5 | calculate | ||||
21 | ZL | 3115 | Sell | 9 | 3115 | |||||
22 | ZM | 3830 | Sell | 11 | 3830 | |||||
23 | ZS | 10694 | Buy | 10 | 10695 | calculate | ||||
24 | ZL | 3115 | Sell | 9 | 3115 | |||||
25 | ZM | 3830 | Sell | 11 | 3830 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H9 | =IF(B9=$C$1,C9,IF(B9=$B$1,C9,IF(B9=$D$1,IF(RIGHT(C9,1)+0=4,C9+1,IF(RIGHT(C9,1)+0=6,C9+1.5,IF(RIGHT(C9,1)+0=2,C9+0.5,IF(RIGHT(C9,1)+0=0,C9))))))) |