Average Sales Using Changing Cell for ISO Week

Hi Everyone,

I am a newb in excel and try my best but this one I cannot figure out. I have a sheet where F6 is my input cell for the week number. By that number it will grab data from the input data sheet and give me for that week what the sales were for each day of that week Mon-Fri. This part works great. The problem is trying to find a formula for the weekly average. What I currently have works grreat as long as the number in F6 is in the current week and my weekly average works, but if a prior week is put in F6 then my weekly average is not valid. As the total sales is adding everything from the input data sheet.

I need a formula for total sales so if a prior week is inputted into F6 then it will total only those numbers up to that week minus the current sales for the week that is in F6. I hope this is making sense.

I have attached a sample file.

Test_Sales.xls

I am thinking maybe I might need some VBA code to do this calculation depending on the variable in F6. Can anyone help on this please? Thank you.

When you say

if a prior week is inputted into F6 then it will total only those numbers up to that week minus the current sales for the week that is in F6

do you mean current week is actually today's week (as in week of May 2, 2012)?

Try:

Please Login or Register  to view this content.

Then is it?
=SUMIF(Sheet2!$E$2:$E$23,"<="&F6,Sheet2!$D$2:$D$23)

I guess I had misunderstood originally... glad it worked now.

The & means concatenation... we are concatenating the number found in F6 to the <= to form the argument... Sumif checks are criteria range for a specified criteria (that argument) and return the sum of the sum range where that criteria was met.

Yes... pretty much....

Check Sumif help for more examples....