Calculating Average with variable range

[DJohanson]

Hello,

I am trying to come up with a way, without going into VBA or macros, to calculate averages with a variable #. Attached is a list of weeks(Col. A) with qty(Col. B). I would like to figure the average in Col. C of corresponding week, back X(D4) weeks. So for example, in week 10, if I entered "10" in D4, I would like to figure a 10 week average. If D4=5, then I would like to figure a 5 week avg, etc...

[Richard Buttrey]

Hi,

Perhaps

Please Login or Register  to view this content.

[DJohanson]

That would work based on the 10 weeks but if I try and change it, it figures the average from the top of the list, down. I need it to start in that week and work upwards. If it is a 5 week avg, in week 10, I would need the average of B6-B10

[Richard Buttrey]

Hi,

What determines how many weeks you want to average? All D4 tells us, if I understand correctly, is the week which is the base week. i.e. how does the system know you want to average 5 weeks starting backwards at week 10?

[DJohanson]

The user will determine how many weeks to average. I just picked 10 weeks to start... might have been a bad choice. D4 is the # of weeks that we need to have averaged. Each week would be the average of the last (D4) weeks.

[Richard Buttrey]

Hi,

Yes I understand that but how does the system know how many weeks to average? I was expecting to see another cell similar to D4 which would hold that value. i.e. the user would make an entry in D4 and this other cell and the system would count back and average the number of D4 weeks starting at the week defined by the other cell.

Or by the 'last (D4) weeks' do you literally mean counting back from the highest (last) week, in your example data week 52?

[Tony Valko]

Maybe this...

=AVERAGE(OFFSET(B2,COUNTA(B2:B53)-1,,-D4))

[kvsrinivasamurthy]

Pl see attached file.

[DJohanson]

I figured it out. I manipulated kvsrinivasamurthy sheet by taking out the If argument and leaving it as an Average/Offset and setting the range indicator one cell below where i wanted the range to start. it seems to work..

Thanks for everyone's help!!

[Tony Valko]

You're welcome. We appreciate the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.