Find Weekly Average Based on Dynamic Date -- Vlookup??

[rkrieg]

I have a cell that contains a date that I want to be able to change. I want another cell to be able to reference that cell/date so it finds the weekly average at that date and a third cell to be able to find the prior 4 week average from that date.

See attachment. Looking for a simple equation out there, thanks for your help!

[somesoldiers]

would the attached work for you?

[rkrieg]

I think you assumed the line items were weeks...they're days.

[somesoldiers]

you only have 16 days on the sheet, how do you know the 4 week average?

[rkrieg]

Its just a template...the weekly average and 4 week should be similar. If I can figure out the week, I'm sure I can figure out the 4 week.

[somesoldiers]

ok, what's a week in your data?- mon- sun, sun to sat,your data starts on a Tue, is it Tue-Mon. When you say "weekly average" and you enter a date that falls on a Wed,do you want to count the previous 4 weeks from Mon to Sun, or the actual previous 4 weeks ie 4 weeks back from the day before (***getting confused myself***).....more info you give us the better we can help you

[rkrieg]

I just want the cell to reference the date in cell B1 so that it calculates the average of that day and the previous 6 days. If I put 3/19 it would calculate the average from that date and through previous 6 days. Does that make sense?

[somesoldiers]

for the weekly average I have changed the helper column H to calculate the average of the selected date and the 6 days previous,obviously we can only start from 15/3 coz the data before that doesn't have 6 days previous data to reference, when you have more data you can add a second helper column to calculate the previous 4 weeks- the vlookup for this is included. are we getting there?

[rkrieg]

Unfortunately, the helper cells aren't ideal since i'm doing this for 60 different metrics haha..

[estige]

Hi, I have a suggested solution without helper columns (using sumproduct).

[arthurbr]

Hi,
enter following array formula in b5 :
=AVERAGE(IF((Sheet2!A3:A18>=Sheet1!B1-6)*(Sheet2!A3:A18<=Sheet1!B1),Sheet2!B3:B18,""))

and confirm with Ctrl+Shift+Enter

adapt the range to your needs. For the last four weeks replace B1-6 by B1-27

[rkrieg]

Hi, I have a suggested solution without helper columns (using sumproduct).

This ALMOST works for some of the columns I'm looking for, but the smaller numbers and/or percents it's giving me a #VALUE error??

Refer to the excel sheet...I'm getting 3 giving me the correct answer, 3 giving me errors.

[DonkeyOte]

Given use of XL2008 I would suggest using AVERAGEIFS in preference to Arrays/Sumproduct :

Please Login or Register  to view this content.

given consecutive nature of your ranges (V:AB) you are able to have a single formula for all rows using INDEX in conjunction with ROWS so as to adjust the column used in each calculation

[rkrieg]

Given use of XL2008 I would suggest using AVERAGEIFS in preference to Arrays/Sumproduct :

Please Login or Register  to view this content.

given consecutive nature of your ranges (V:AB) you are able to have a single formula for all rows using INDEX in conjunction with ROWS so as to adjust the column used in each calculation

I'm still getting a #DIV/0! error on the same three.

[DonkeyOte]

The suggested formula works on your sample file without incident and returns the same results as those calculated manually.

Please post a sample with suggested formulae in place which demonstrates the error.