How do I lookup data from multiple date ranges from a single table?

Jordan213

New Member
Joined
Apr 4, 2013
Messages
20
I manage inventory for our company, and I frequently have need to calculate descriptive statistics for various types of demand and sales data. We use Quickbooks POS, which doesn't offer very sophisticated reporting tools. I export data to Excel to work with it. For example, if I want to create a confidence interval estimate for demand, I have to export sales data for each period that I am using as a sample. I also smooth the data by creating a weighted average.

There are several problems with my current method of looking up data from several tables. It becomes extremely complicated, as I have to create one table in one worksheet for each period (I use a week), then create another table to summarize the raw data, then create another table to create weighted values, then create another table to calculate the standard deviation of the weighted average. I realize I could do it in one table, but it'd have something like 50 columns.

Analyzing data from a single table would allow me to readily change the resolution of data, from the current one week to several days or a day, or to one month, or whatever I want. The workbook is very slow, I'm rewriting it to use index/match instead of vlookup in many instances, but it still lags. Updating data is also hugely cumbersome. I would like to apply exponential smoothing to create a forecast, but my workbook is already far too complicated. Adding more intermediate tables would be a nightmare.

What I want to do is export the entire dataset encompassing every period I want to analyze and place it into one table. The data would be presented as item #, date of sale, quantity sold, and price extension. After export I want to use another table to designate which periods I want summarized. This would consist of period name/number, start date, end date. I want to tell Excel to lookup and sum data between the start date and the end date. The only thing I would have to update would be the raw data, and the designated search periods. I would imagine something like sumif would be perfect, except that I want it to summarize data that falls within a range of values (dates), not a single value. I also want it to run at a decent pace. The data I would be exporting would consist of say, 10,000 rows and 4 columns.

Any ideas on how to implement this in Excel? How much easier would this be if I were somewhat knowledgeable in Access and migrated it there? I would like to do a lot more with the data we have.
 
Last edited:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
If your data is in some sort of database, then export the whole lot to excel. Then analyse by what you want using sumproduct or pivot tables. Put some test data up so we can look at it.
 

Jordan213

New Member
Joined
Apr 4, 2013
Messages
20
Thanks for the reply. I unfortunately don't have much experience with sumproduct or pivot tables, I'm reading up on them now. Silly question: How do I post test data here? Raw text?
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
copy part of your spreadsheet and just paste it here - its magic
 

Jordan213

New Member
Joined
Apr 4, 2013
Messages
20

ADVERTISEMENT

copy part of your spreadsheet and just paste it here - its magic

Darned right it's magic. Here's a sample of the raw dataset from which I want to lookup.

DateItem #Qty SoldExt price ( $)
3/30/201341160.00
3/30/20132044121.95
3/30/20131493113.97
3/30/20132036132.93
3/30/20133366132.93
3/30/20133771134.92

<tbody>
</tbody>





Right now I'm summarizing the data by creating a lookup that calls an individual table with data from each period. This is a sample of the simple summary table. I redacted the other periods for simplicity. After summarizing this data I then create another table, which looks up data from this table, and from a table with period weights to create a weighted average. Then I have another table to calculate the standard deviation of the weighted average.

Item #P1P2P3AVGSTDEV
302412001400500025331746
314500000
41971875221015070
963-20856285404

<tbody>
</tbody>

<tbody>
</tbody>

I would prefer not to use intermediate tables, but I don't want the formulas to be so complicated that they can't be understood.
 
Last edited:

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
From my limited knowledge of statistics, the std deviations of averages is dodgy ground. You would be better using a (statistical process) control chart for each item each period.

Sorry I still can't follow what you are trying to do with the data.
 

Jordan213

New Member
Joined
Apr 4, 2013
Messages
20
I'm not calculating the standard deviation of several averages, I'm calculating the standard deviation of demand using x_i less the weighted average and accounting for period weights, not the arithmetic average. I eventually want to use the data to create a demand forecast using exponential smoothing. I also want to make it easier and faster to update the data. Right now it's extremely cumbersome because of the number of and interdependence of tables. Currently, I create a lookup in each period column (P1, P2, P3....) that searches a corresponding table for sales data. I use 14 periods, so the summary table is extracting data from 14 separate tables. I want to summarize the data by looking for data in a single table that encompasses all of the raw sales data, using the criteria of item # and a sales date falling within a defined start and end date.

Example: Period 1 is 03/30/13 - 04/06/13/ P1 is defined in a table with other period start and end dates. I want to return a summary of all sales of items # 1, 2, 3, 4, etc. within period 1, then within period 2, and so on, by telling Excel to search a single table with raw sales data.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top