In the first table below, I have a trade log with unique trades. In a separate table, also below, I need to calculate a monthly total 'HEDGE ZONE' cost per period, based on a summation of each individual trades product of 'DAILY $$' and number of days the trade flowed in that given month where:
- Some trades will not flow completely through the period based on 'START' and 'END' dates
- Trades are broken up by 'PRODUCT' and either flow every day (7x), weekdays only (5x), or weekends only (2x)
How can I go about calculating all of these together without creating a separate table?
- Some trades will not flow completely through the period based on 'START' and 'END' dates
- Trades are broken up by 'PRODUCT' and either flow every day (7x), weekdays only (5x), or weekends only (2x)
How can I go about calculating all of these together without creating a separate table?
| Book1 | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | PERIOD | ERCT-H | ERCT-N | ERCT-S | ERCT-W | ||
| 2 | Sep 2020 | ||||||
| 3 | Oct 2020 | ||||||
| 4 | Nov 2020 | ||||||
| 5 | Dec 2020 | ||||||
| 6 | Jan 2021 | ||||||
| 7 | Feb 2021 | ||||||
| 8 | Mar 2021 | ||||||
| 9 | Apr 2021 | ||||||
| 10 | May 2021 | ||||||
| 11 | Jun 2021 | ||||||
| 12 | Jul 2021 | ||||||
| 13 | Aug 2021 | ||||||
Sheet2 | |||||||
| Book1 | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | START | END | PRODUCT | TRADE ZONE | HEDGE ZONE | QTY | PRC | ENTERED | MODIFIED | TOTAL (MWH) | TOTAL ($$) | DAILY ($$) | ||
| 2 | 7/1/2020 | 6/30/2022 | 7x16 | ERCOT_H | ERCT-H | 1.000 | $41.25 | 6/17/2020 | 6/17/2020 | 11,680.00 | $ 481,800 | $ (660) | ||
| 3 | 8/1/2020 | 7/31/2023 | 7x16 | ERCOT_H | ERCT-H | 2.000 | $39.80 | 6/17/2020 | 6/17/2020 | 35,040.00 | $ 1,394,592 | $ (1,274) | ||
| 4 | 9/1/2020 | 9/15/2020 | 5x16 | ERCOT_H | ERCT-H | 4.000 | $44.55 | 6/24/2020 | 6/24/2020 | 704.00 | $ 31,363 | $ (2,851) | ||
| 5 | 9/1/2020 | 9/30/2020 | 7x8 | ERCOT_H | ERCT-H | 1.500 | $44.55 | 6/24/2020 | 6/24/2020 | 360.00 | $ 16,038 | $ (535) | ||
| 6 | 9/1/2020 | 9/30/2020 | 7x16 | ERCOT_N | ERCT-N | 3.000 | $40.80 | 6/24/2020 | 6/24/2020 | 1,440.00 | $ 58,752 | $ (1,958) | ||
| 7 | 9/1/2020 | 9/20/2020 | 2x16 | ERCOT_N | ERCT-N | 1.500 | $40.80 | 6/24/2020 | 6/24/2020 | 144.00 | $ 5,875 | $ (979) | ||
| 8 | 9/1/2020 | 9/30/2020 | 7x16 | ERCOT_H | ERCT-H | 1.800 | $36.40 | 6/24/2020 | 6/24/2020 | 864.00 | $ 31,450 | $ (1,048) | ||
| 9 | 9/1/2020 | 9/30/2020 | 7x8 | ERCOT_H | ERCT-H | 1.000 | $36.40 | 6/24/2020 | 6/24/2020 | 240.00 | $ 8,736 | $ (291) | ||
| 10 | 10/1/2020 | 10/15/2020 | 7x24 | ERCOT_H | ERCT-H | 2.000 | $44.55 | 6/24/2020 | 6/24/2020 | 720.00 | $ 32,076 | $ (2,138) | ||
| 11 | 10/15/2020 | 10/31/2020 | 7x16 | ERCOT_H | ERCT-H | 1.000 | $44.55 | 6/24/2020 | 6/24/2020 | 272.00 | $ 12,118 | $ (713) | ||
| 12 | 10/1/2020 | 1/31/2021 | 7x16 | ERCOT_N | ERCT-N | 1.500 | $40.80 | 6/24/2020 | 6/24/2020 | 2,952.00 | $ 120,442 | $ (979) | ||
| 13 | 10/1/2020 | 1/31/2021 | 7x8 | ERCOT_N | ERCT-N | 1.000 | $40.80 | 6/24/2020 | 6/24/2020 | 984.00 | $ 40,147 | $ (326) | ||
| 14 | 10/1/2020 | 10/31/2020 | 5x16 | ERCOT_H | ERCT-H | 0.900 | $42.05 | 6/24/2020 | 6/24/2020 | 316.80 | $ 13,321 | $ (606) | ||
| 15 | 10/1/2020 | 10/31/2020 | 2x16 | ERCOT_H | ERCT-H | 0.600 | $42.05 | 6/24/2020 | 6/24/2020 | 86.40 | $ 3,633 | $ (404) | ||
| 16 | 10/1/2020 | 10/31/2020 | 7x8 | ERCOT_H | ERCT-H | 0.700 | $42.05 | 6/24/2020 | 6/24/2020 | 173.60 | $ 7,300 | $ (235) | ||
| 17 | 11/1/2020 | 5/31/2021 | 7x16 | ERCOT_H | ERCT-H | 1.500 | $44.55 | 6/24/2020 | 6/24/2020 | 5,088.00 | $ 226,670 | $ (1,069) | ||
| 18 | 11/1/2020 | 5/31/2021 | 7x8 | ERCOT_H | ERCT-H | 1.000 | $44.55 | 6/24/2020 | 6/24/2020 | 1,696.00 | $ 75,557 | $ (356) | ||
| 19 | 11/1/2020 | 11/30/2020 | 7x24 | ERCOT_H | ERCT-H | 0.500 | $42.05 | 6/24/2020 | 6/24/2020 | 360.00 | $ 15,138 | $ (505) | ||
| 20 | 12/1/2020 | 12/31/2020 | 7x24 | ERCOT_H | ERCT-H | 0.700 | $42.05 | 6/24/2020 | 6/24/2020 | 520.80 | $ 21,900 | $ (706) | ||
| 21 | 1/1/2021 | 1/31/2021 | 7x16 | ERCOT_H | ERCT-H | 0.600 | $42.05 | 6/24/2020 | 6/24/2020 | 297.60 | $ 12,514 | $ (404) | ||
| 22 | 1/1/2021 | 1/31/2021 | 7x8 | ERCOT_H | ERCT-H | 0.700 | $42.05 | 6/24/2020 | 6/24/2020 | 173.60 | $ 7,300 | $ (235) | ||
| 23 | 2/1/2021 | 2/28/2021 | 5x16 | ERCOT_N | ERCT-N | 1.500 | $40.80 | 6/24/2020 | 6/24/2020 | 480.00 | $ 19,584 | $ (979) | ||
| 24 | 2/1/2021 | 2/28/2021 | 7x8 | ERCOT_N | ERCT-N | 1.000 | $40.80 | 6/24/2020 | 6/24/2020 | 224.00 | $ 9,139 | $ (326) | ||
| 25 | 2/1/2021 | 2/28/2021 | 2x16 | ERCOT_N | ERCT-N | 1.000 | $40.80 | 6/24/2020 | 6/24/2020 | 128.00 | $ 5,222 | $ (653) | ||
| 26 | 3/1/2021 | 3/31/2021 | 7x24 | ERCOT_N | ERCT-N | 1.000 | $40.80 | 6/24/2020 | 6/24/2020 | 744.00 | $ 30,355 | $ (979) | ||
| 27 | 4/1/2021 | 5/31/2021 | 7x16 | ERCOT_N | ERCT-N | 1.000 | $40.80 | 6/24/2020 | 6/24/2020 | 976.00 | $ 39,821 | $ (653) | ||
| 28 | 4/1/2021 | 5/31/2021 | 7x8 | ERCOT_N | ERCT-N | 0.500 | $40.80 | 6/24/2020 | 6/24/2020 | 244.00 | $ 9,955 | $ (163) | ||
| 29 | 6/1/2021 | 6/30/2021 | 7x16 | ERCOT_H | ERCT-H | 1.000 | $44.55 | 6/24/2020 | 6/24/2020 | 480.00 | $ 21,384 | $ (713) | ||
| 30 | 6/1/2021 | 6/30/2021 | 7x8 | ERCOT_H | ERCT-H | 0.500 | $44.55 | 6/24/2020 | 6/24/2020 | 120.00 | $ 5,346 | $ (178) | ||
| 31 | 6/1/2021 | 7/31/2021 | 7x24 | ERCOT_N | ERCT-N | 0.500 | $40.80 | 6/24/2020 | 6/24/2020 | 732.00 | $ 29,866 | $ (490) | ||
| 32 | 7/1/2021 | 7/31/2021 | 7x24 | ERCOT_H | ERCT-H | 0.500 | $44.55 | 6/24/2020 | 6/24/2020 | 372.00 | $ 16,573 | $ (535) | ||
Sheet1 | ||||||||||||||
