not sure if this can be done rolling averages
 

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

not sure if this can be done rolling averages
 

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

not sure if this can be done rolling averages
 

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" wrote in message
...
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