Right, so basically I want to average daily prices of a commodity for a giving pricing period.
However, the period changes.
What I'm envisioning is as such: I tell excel the START DATE on cell B3, END DATE on B4, and B5 is "=B4-B3+1" and that gives the number of days (counting inclusively, hence the +1). So for example, for a pricing period of Sep10-13, that's 4 days, so B5 = 4.
Now, using the same example, I want a macro that adds values in the B column, starting from B7, as such:
So basically it's +1 day all the time until it reaches the END DATE (or until it increments to the value in B5 MINUS 1 --- that should be the cap.... i.e. notice how it's +0, then +1, +2 and finally +3).
Should the period be changed to Sep12-13, for example, excel would automatically remove the last 2 rows and their contents, and starting from B7 only the following would be shown:
(and the value in B5 would be 2).
Does that make sense?
I've been trying to do this but I am getting nowhere... I am sure one of you guys can help me out?
After that is all done, I would manually input all the price indexes for each day on column C (starting from C7).
The average is calculate on cell D3. The formula is basically the sum of all the rows with price indexes (not sure how to get this to automatically expand or contract, so I'm using the entire C column) and then divides it by =COUNTA of the entire C column (counts everything that isn't blank). If you know of a cleaner/better way to do this, expanding and contracting the C column as it goes, that'd be nice, otherwise this is fine. I'm more worried about the above problem (inserting rows and incrementing).
Thanks a lot peeps!!