not sure if this can be done rolling averages

[Aaron H]

Hello everyone, thanks all in advance for any comments and help.

I have been asked to create a spread sheet for work that tracks the number
of customers that come into our buisness througout the 24hour day, recored
the number of customers and then create a rolling three week average. an
examble of this would be monday april 18th 10 people were in the store,
monday april 25 5 people came into the store and moday may 2nd 10 more people
came into the store so the average of this is 3..were my problem is I need a
way to automatically drop april 18th out of the equation when i put in the
data for may 9th? is there a way to automatically do this?
again thanks in advance
Aaron

[Aaron H]

this worked awesome, thanks! would you mind explaing what the stuff means?

"Duke Carey" wrote:

> Assuming you are going to enter these by date going down the sheet, starting
> in row 2, and assuming you have the date in col A and the # of custoemrs in
> col B. Then, in cell C3 try
>
> =AVERAGE(OFFSET(C2,-MIN(19,ROW(B2)-2),-1,MIN(20,ROW(B2)-1),1))
>
>
> "Aaron H" wrote:
>
> > Hello everyone, thanks all in advance for any comments and help.
> >
> > I have been asked to create a spread sheet for work that tracks the number
> > of customers that come into our buisness througout the 24hour day, recored
> > the number of customers and then create a rolling three week average. an
> > examble of this would be monday april 18th 10 people were in the store,
> > monday april 25 5 people came into the store and moday may 2nd 10 more people
> > came into the store so the average of this is 3..were my problem is I need a
> > way to automatically drop april 18th out of the equation when i put in the
> > data for may 9th? is there a way to automatically do this?
> > again thanks in advance
> > Aaron

[Duke Carey]

The OFFSET function allows you to determine a range relative to a reference
point - so many rows & columns away, and so many rows high & columns wide.

For the cell with the average function, you want to use a range of 20 cells,
but you can't use that many until you have data down to row 21. The Offset,
Min, & Row functions tells Excel to use as many cells as possible, given the
row number from which you're calculating the average, up to a maximum of 20
rows.

Don't have time for a more thorough explanation right now. Sorry

"Aaron H" <AaronH@discussions.microsoft.com> wrote in message
news:B7270B5D-B22A-4DFA-AFE8-D0B905942BD6@microsoft.com...
> this worked awesome, thanks! would you mind explaing what the stuff means?
>
> "Duke Carey" wrote:
>
>> Assuming you are going to enter these by date going down the sheet,
>> starting
>> in row 2, and assuming you have the date in col A and the # of custoemrs
>> in
>> col B. Then, in cell C3 try
>>
>> =AVERAGE(OFFSET(C2,-MIN(19,ROW(B2)-2),-1,MIN(20,ROW(B2)-1),1))
>>
>>
>> "Aaron H" wrote:
>>
>> > Hello everyone, thanks all in advance for any comments and help.
>> >
>> > I have been asked to create a spread sheet for work that tracks the
>> > number
>> > of customers that come into our buisness througout the 24hour day,
>> > recored
>> > the number of customers and then create a rolling three week average.
>> > an
>> > examble of this would be monday april 18th 10 people were in the store,
>> > monday april 25 5 people came into the store and moday may 2nd 10 more
>> > people
>> > came into the store so the average of this is 3..were my problem is I
>> > need a
>> > way to automatically drop april 18th out of the equation when i put in
>> > the
>> > data for may 9th? is there a way to automatically do this?
>> > again thanks in advance
>> > Aaron