Solved

# Formula to create weekly buckets with several criteria.

Posted on 2003-03-13
Medium Priority
485 Views
I have a large list of data for sales by product code on certain days. (sample pasted below)  I need to compare a master list of codes to the sales list of codes and sum the amount sold for each product within a 7 day weekly bucket.

Criteria:
Code on master list matches code on sales list.
Date within 7 day bucket.
Sum each code for each week.

Item Number     Eff Date     Sales
101743           8/20/2002             20799
101744           1/29/2002             4983
101750           4/19/2002             1723
128730           10/7/2002             1357
151773           6/7/2002             2741
1529152           11/5/2002             2576
161714           4/26/2002             604
1618152           9/18/2002             262
1618153           2/11/2002             2035
162084           3/27/2002             3750
162261           1/25/2002             6026
1624151           6/12/2002             828
168578           8/13/2002             10834

I have been working on getting one single formula for this for quit a while but have not been able to derive it.

0
Question by:tab26
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 16

Accepted Solution

sebastienm earned 300 total points
ID: 8129370
tab26,
If you are ok with using a pivot table to summarize the data:

Say Item Num is in column A, Eff Date is in column B and Sales in column C of sheeet Sales... header is in row 1, data starts in row 2.
Say, in sheet Master, Item Num are in column A

I would add a column 'Criteria' in column D of sheet Sales with a formula:
D1: Criteria
D2: = IF( ISNA(VLOOKUP(\$A2,Master!\$A:\$A,1,FALSE)), 0, 1)
ie. if product in A2 exists in Master then return 1 else 0.
Copy the formula down along the data.

Now, add a column, say E, with the year, and a column, Say F, with the week number.
Note: to be able to use the WEEKNUM() function as described bellow, you need to have the Analysis Toolpack add-in checked: menu Tools > Add-Ins, check the Analysis Toolpack.
E1: Year
E2: =YEAR(B2)
F1: Week
F2: =WEEKNUM(B2)
Copy these formulas down along the data.

Finally, create a pivot table as follow:
- Select the data (including headers)
- menu Data > Pivot Table
- Pivot table setup:
in Column fiels: Year , Week
in Row field: Item Num
in Page Field: Criteria
- now, when the Pivot table displays, choose 'Criteria' equals 1 (i.e. sales product matches master product)

I hope this helps,
Sebastien
0

Author Comment

ID: 8129676
Works like a charm.  I think the whol key was having the Analysis Toolpack checked.  Thank you very much.

Terry
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month10 days, 2 hours left to enroll