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

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

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