7 previous days average

Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average value, of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

Change column to suit

=AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

Hi there,

So, I pasted your function into my column and it worked well, however, how
can i adjust this function so that it is dynamic. That is, regardless of
where the formula is placed in the row for Oct 1, it calculates 7 days
average prior to Oct 1. If i place it in row with date Sep 5, it will
calculate 7 days average prior to Sep 5.

Thanks in advance for your help. It is really appreciated.

--
Carlee


"Don Guillett" wrote:

Change column to suit

=AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

Place anywhere on the sheet except in column aj to average the last 7 rows
of column aj.
=AVERAGE(OFFSET($aj$1,COUNT($aj$1:$aj$1000),,-7,))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
news
Hi there,

So, I pasted your function into my column and it worked well, however, how
can i adjust this function so that it is dynamic. That is, regardless of
where the formula is placed in the row for Oct 1, it calculates 7 days
average prior to Oct 1. If i place it in row with date Sep 5, it will
calculate 7 days average prior to Sep 5.

Thanks in advance for your help. It is really appreciated.

--
Carlee


"Don Guillett" wrote:

Change column to suit

=AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average
value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

hi there,

Still no luck. Here is the code that exists in the column now.

=SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78))

Problem is that this function does not average, AND it does look at only the
previous 7 days of data in column AJ. Can you assist me?
--
Carlee


"Don Guillett" wrote:

Place anywhere on the sheet except in column aj to average the last 7 rows
of column aj.
=AVERAGE(OFFSET($aj$1,COUNT($aj$1:$aj$1000),,-7,))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
news
Hi there,

So, I pasted your function into my column and it worked well, however, how
can i adjust this function so that it is dynamic. That is, regardless of
where the formula is placed in the row for Oct 1, it calculates 7 days
average prior to Oct 1. If i place it in row with date Sep 5, it will
calculate 7 days average prior to Sep 5.

Thanks in advance for your help. It is really appreciated.

--
Carlee


"Don Guillett" wrote:

Change column to suit

=AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average
value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

How about this

=AVERAGE(INDEX($AK$1:$AK$1000,ROW()):INDEX($AK$1:$ AK$1000,MAX(1,ROW()-6)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Carlee" wrote in message
...
hi there,

Still no luck. Here is the code that exists in the column now.

=SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78))

Problem is that this function does not average, AND it does look at only
the
previous 7 days of data in column AJ. Can you assist me?
--
Carlee


"Don Guillett" wrote:

Place anywhere on the sheet except in column aj to average the last 7
rows
of column aj.
=AVERAGE(OFFSET($aj$1,COUNT($aj$1:$aj$1000),,-7,))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
news
Hi there,

So, I pasted your function into my column and it worked well, however,
how
can i adjust this function so that it is dynamic. That is, regardless
of
where the formula is placed in the row for Oct 1, it calculates 7 days
average prior to Oct 1. If i place it in row with date Sep 5, it will
calculate 7 days average prior to Sep 5.

Thanks in advance for your help. It is really appreciated.

--
Carlee


"Don Guillett" wrote:

Change column to suit

=AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average
value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

HI Bob,

Can you do the same thing to this function:

=SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78))

Essentially, I need to have this calculation listed in Column AK. Whatever
the row this calculation is placed in column AK, I need the function to run
based on the seven prior days for columns r and aj. Make sense?
--
Carlee


"Bob Phillips" wrote:

How about this

=AVERAGE(INDEX($AK$1:$AK$1000,ROW()):INDEX($AK$1:$ AK$1000,MAX(1,ROW()-6)))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Carlee" wrote in message
...
hi there,

Still no luck. Here is the code that exists in the column now.

=SUM($R$10:R78)/(3.785/1000*SUM($AJ$10:AJ78))

Problem is that this function does not average, AND it does look at only
the
previous 7 days of data in column AJ. Can you assist me?
--
Carlee


"Don Guillett" wrote:

Place anywhere on the sheet except in column aj to average the last 7
rows
of column aj.
=AVERAGE(OFFSET($aj$1,COUNT($aj$1:$aj$1000),,-7,))


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
news Hi there,

So, I pasted your function into my column and it worked well, however,
how
can i adjust this function so that it is dynamic. That is, regardless
of
where the formula is placed in the row for Oct 1, it calculates 7 days
average prior to Oct 1. If i place it in row with date Sep 5, it will
calculate 7 days average prior to Sep 5.

Thanks in advance for your help. It is really appreciated.

--
Carlee


"Don Guillett" wrote:

Change column to suit

=AVERAGE(OFFSET($F$1,COUNT($F$1:$F$1000),,-7,))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average
value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

Hi Carlee,

Unless I'm missing something here (quite probable!)

All you need to do is put this in AK9
=IF(AJ9="","",AVERAGE(AJ3:AJ9))
and drag it down as far as your future needs require.

The references update as you drag to give you
a rolling 7 cell average.

HTH
Martin


"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

it worked !! It worked!! It worked!!!

Thank you so very much for all of your help.
--
Carlee


"MartinW" wrote:

Hi Carlee,

Unless I'm missing something here (quite probable!)

All you need to do is put this in AK9
=IF(AJ9="","",AVERAGE(AJ3:AJ9))
and drag it down as far as your future needs require.

The references update as you drag to give you
a rolling 7 cell average.

HTH
Martin


"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee

Glad to hear that Carlee!

Regards
Martin

"Carlee" wrote in message
...
it worked !! It worked!! It worked!!!

Thank you so very much for all of your help.
--
Carlee


"MartinW" wrote:

Hi Carlee,

Unless I'm missing something here (quite probable!)

All you need to do is put this in AK9
=IF(AJ9="","",AVERAGE(AJ3:AJ9))
and drag it down as far as your future needs require.

The references update as you drag to give you
a rolling 7 cell average.

HTH
Martin


"Carlee" wrote in message
...
Hi All,

Question: What function can i use to create an average value of the
previous 7 days?

Column AJ contains data, which is added to everyday
Column B contains the date

In Column AK, I want to have a value, which calculates the average
value,
of
the 7 previous values entered in column AJ.

Data starts in row 3 for all columns.
--
Carlee