Average last week's data based on current date and certain condition..

Question

ok so this is what i am trying to do:

i have a condition on column A where it shows Actual or Forecast

in column B i have a range of dates broken down daily.

in Column C i have number of transactions broken down daily.

What i want to do is to average last week's transactions based on the last "Actual" date. The formula must be able to locate which date is the last actual one (meaning the next date will be a forecast one or blank if it reached last day,) and average the transactions of the last "actual" week. 

Is it possible to do that on one formula without using pivot tables?

Answer 1

sure you can find a sample excel here:  http://dl.dropbox.com/u/15062245/sample.xlsx

 

the average number of transactions for the last week is 190,099 (cells D54:D60)

 

but if i enter actual data for today lets say, i want it to average cells D55:D61. The formula has to be based on the last "Actual" date and count the average of the last "Actual" week.

Ok, it looks like each day starts as a forecast and then is changed to actual.

So, it looks like you want to get the average starting from the last (bottom-most) instance of actual then going up the column for a total of 7 cells.

Assuming there will always be at least 7 cells that contain actual...

=AVERAGE(OFFSET(D4,COUNTIF(A4:A500,"actual")-1,,-7))

Use a big enough range size in the COUNTIF function to allow for future data addition.

--

Biff

Microsoft Excel MVP

Answer 2

thanks this worked perfectly!

You're welcome. Thanks for the feedback!

--

Biff

Microsoft Excel MVP

Answer 3

sure you can find a sample excel here:  http://dl.dropbox.com/u/15062245/sample.xlsx

 

the average number of transactions for the last week is 190,099 (cells D54:D60)

 

but if i enter actual data for today lets say, i want it to average cells D55:D61. The formula has to be based on the last "Actual" date and count the average of the last "Actual" week.

Ok, it looks like each day starts as a forecast and then is changed to actual.

So, it looks like you want to get the average starting from the last (bottom-most) instance of actual then going up the column for a total of 7 cells.

Assuming there will always be at least 7 cells that contain actual...

=AVERAGE(OFFSET(D4,COUNTIF(A4:A500,"actual")-1,,-7))

Use a big enough range size in the COUNTIF function to allow for future data addition.

--

Biff

Microsoft Excel MVP

thanks this worked perfectly!

Answer 4

sure you can find a sample excel here:  http://dl.dropbox.com/u/15062245/sample.xlsx

the average number of transactions for the last week is 190,099 (cells D54:D60)

but if i enter actual data for today lets say, i want it to average cells D55:D61. The formula has to be based on the last "Actual" date and count the average of the last "Actual" week.

Answer 5

ok so this is what i am trying to do:

 

i have a condition on column A where it shows Actual or Forecast

in column B i have a range of dates broken down daily.

in Column C i have number of transactions broken down daily.

What i want to do is to average last week's transactions based on the last "Actual" date. The formula must be able to locate which date is the last actual one (meaning the next date will be a forecast one or blank if it reached last day,) and average the transactions of the last "actual" week. 

Is it possible to do that on one formula without using pivot tables?

Can you post some sample data and tell us what result you expect?

--

Biff

Microsoft Excel MVP