Running average based on certain number of weeks

[leftee08]

Hi - I have been trying to figure out how I can create a formula for an average of numbers based on one number in a cell. For example - I have 15 weeks worth of data going from C3:R3. I have a number in cell B3 that represents the number weeks I need to average. So, B3 = 8 then I need to average cells C3:J3 (result in J4). Then I need to keep that going and average the next 8weeks (D3:K3) then E3:L3 .. and so on. I can dothis simply by the average function and dragging it across. However B3 will be changing often. Its not a constant number. I tried the formula =AVERAGE(OFFSET(C3,COUNT(C3:R3)-8,0,-8)) but it did not work. I transposed the numbers and then used this =AVERAGE(OFFSET(B9,COUNT($B$9:$B$50)-8,0,-8)). This worked. Is ther a way I can use the first one where the data goes from C3:R3?

Can anyone help me???

 

[NARAYANK991]

Hi ,

Can you try this ?

=AVERAGE(OFFSET(C3,,COUNT(C3:OFFSET(C3,,$B$3-1))-1,,-$B$3))

Narayan

 

[leftee08]

This works PERFECT !!! Makes sense to now that I can see. Thank you so much !!!