7 previous days average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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,))
 
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?
 
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 said:
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?
Click to expand...
 
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?
 
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
 
Back
Top