The file I have is at 7megs and will more then likely get into the 20s by the time this project is done. I would like to manipulate the table below to look like the one below. I am doing it with
=IF(IF(AND(ISBLANK(D$20000),ISBLANK($B20001)),"",SUMPRODUCT(--($D$6:$D$18676=D$20000),--($E$6:$E$18676=$B20001)*($F$6:$F$18676)))=0,"",(IF(AND(ISBLANK(D$20000),ISBLANK($B20001)),"",SUMPRODUCT(--($D$6:$D$18676=D$20000),--($E$6:$E$18676=$B20001)*($F$6:$F$18676)))))
right now but the links wont allow me to delete the original table so i may save some space. as the file gotten bigger it is taking longer and longer for the formulas to calculate. any help would be much appreciated.
I was thinking of an array but not sure how to make that happen given it has to format based on monthly buckets.
what i would like for it to look like.
=IF(IF(AND(ISBLANK(D$20000),ISBLANK($B20001)),"",SUMPRODUCT(--($D$6:$D$18676=D$20000),--($E$6:$E$18676=$B20001)*($F$6:$F$18676)))=0,"",(IF(AND(ISBLANK(D$20000),ISBLANK($B20001)),"",SUMPRODUCT(--($D$6:$D$18676=D$20000),--($E$6:$E$18676=$B20001)*($F$6:$F$18676)))))
right now but the links wont allow me to delete the original table so i may save some space. as the file gotten bigger it is taking longer and longer for the formulas to calculate. any help would be much appreciated.
I was thinking of an array but not sure how to make that happen given it has to format based on monthly buckets.
Correl_Temp_tEMPLATE.xls | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
5 | cmdty_code | mkt_code | price_source_code | trading_prd | price_quote_date | avg_closed_price | ||
6 | WTI | NYMEX | INTERNAL | 200804 | 3/31/2008 | 104.48 | ||
7 | WTI | NYMEX | INTERNAL | 200805 | 3/31/2008 | 101.58 | ||
8 | WTI | NYMEX | INTERNAL | 200805 | 4/1/2008 | 100.98 | ||
9 | WTI | NYMEX | INTERNAL | 200805 | 4/2/2008 | 104.83 | ||
10 | WTI | NYMEX | INTERNAL | 200805 | 4/3/2008 | 103.83 | ||
11 | WTI | NYMEX | INTERNAL | 200805 | 4/4/2008 | 106.23 | ||
12 | WTI | NYMEX | INTERNAL | 200805 | 4/7/2008 | 109.09 | ||
13 | WTI | NYMEX | INTERNAL | 200805 | 4/8/2008 | 108.5 | ||
14 | WTI | NYMEX | INTERNAL | 200805 | 4/9/2008 | 110.87 | ||
15 | WTI | NYMEX | INTERNAL | 200805 | 4/10/2008 | 110.11 | ||
Prices |
what i would like for it to look like.
Correl_Temp_tEMPLATE.xls | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
20000 | 200804 | 200805 | 200806 | 200807 | |||
20001 | 3/31/2008 | 104.48 | 101.58 | 101.09 | 100.56 | ||
20002 | 3/31/2008 | 104.48 | 101.58 | 101.09 | 100.56 | ||
20003 | 4/1/2008 | 100.98 | 100.5 | 100 | |||
20004 | 4/2/2008 | 104.83 | 104.28 | 103.72 | |||
20005 | 4/3/2008 | 103.83 | 103.26 | 102.63 | |||
20006 | 4/4/2008 | 106.23 | 105.76 | 105.19 | |||
20007 | 4/7/2008 | 109.09 | 108.55 | 107.89 | |||
20008 | 4/8/2008 | 108.5 | 107.86 | 107.14 | |||
20009 | 4/9/2008 | 110.87 | 110.19 | 109.4 | |||
20010 | 4/10/2008 | 110.11 | 109.57 | 108.93 | |||
20011 | 4/11/2008 | 110.14 | 109.71 | 109.19 | |||
20012 | 4/14/2008 | 111.76 | 111.17 | 110.45 | |||
Prices |