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