Price Changes

I have to make 'New Price' based on the 'Period' changes. For example if the number in 'Period' is 3 that means the 'New Price' for Jan and feb '13 are exactly the same as 'Market Price', but on March 13 is the average price of the previous two months. Important note: If the number in 'Period' is changed to other number, I need to make sure that the 'New Price' will automatically changed. For example if the 'Period' is 4 that means, the first four months of 'New Price' will be the same as the 'Market Price' but the in the fifth month will be the average price of the previous four. Please help.
Exercise.xlsx

I don't like that the rule you use to describe your change is just a list of examples that the actual rule has to be deduced from.

Here is what I think you want it to do:

New Price is:
IF the month of the record is less than the "Period"
TRUE: Market Price
FALSE: The average of all months less than the "Period" (assumed: in the same year).

Anyway try this in C5 and pull down:

=IF(MONTH(A6)<Period, B6, SUMPRODUCT(Market_Price,--(MONTH(dates)<Period),--(YEAR(dates)=YEAR(A6))) / SUMPRODUCT(--(MONTH(dates)<Period),--(YEAR(dates)=YEAR(A6))))

I wanted to use AVERAGEIFS, but since you needed to operate on the "dates" range, that wasn't feasible.

So you're saying that every nth item is the average of the n-1 items before it; and all the non-nth items are the corresponding "market price"?

Like so:
where n = 3
1 = market price
2 = market price
3 = average(1,2)
4 = market price
5 = market price
6 = average(4,5)

where n = 4
1 = market price
2 = market price
3 = market price
4 = average(1,2,3)
5 = market price
6 = market price
7 = market price
8 = average(5, 6, 7)
etc

Like that?

ben_hensel, yes.. That's what I meant. nflsales, great help. I appreciate it!! Thanks alot.