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

#### Jordan213

##### New Member
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:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.

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?

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

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.

 Date Item # Qty Sold Ext price ( \$) 3/30/2013 4 1 160.00 3/30/2013 2044 1 21.95 3/30/2013 1493 1 13.97 3/30/2013 2036 1 32.93 3/30/2013 3366 1 32.93 3/30/2013 3771 1 34.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 # P1 P2 P3 AVG STDEV 3024 1200 1400 5000 2533 1746 3145 0 0 0 0 0 4197 187 52 210 150 70 963 -2 0 856 285 404

<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:
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.

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:

Replies
2
Views
182
Replies
0
Views
141
Replies
1
Views
198
Replies
1
Views
145
Replies
8
Views
166

1,221,094
Messages
6,157,889
Members
451,448
Latest member
SAM1961

### 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.

### Which adblocker are you using?

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

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