Question on Row Based Data Analysis & Calculations Based On User Input

[jdlev]

Hi Guys,

I've got excel 2010. Here's what I'm after.

I'm trying to simplify a spreadsheet of startup costs. Basically, I'm looking to analyze the startup expenses from a number of different perspectives. Each cost has a few columns: Account code, description, price, vendor, type of asset, and depreciation method to be used. Here's some of the parameters I would like to be able to set, but I have no idea where to begin:

User Inputs:

• Cost thresholds
• Which categories are included in the calculation
• Which types are included in the calculation

Let's say there are 150 rows of startup costs, what would be the best way to configure a few of the user inputs, and have it update a completely different table with the information? Say for instance, I wanted to narrow the list down to all fixed assets, over $500, with account code 300. How would I have excel run the data, and then save the output (like say the sum of the prices) to a new table - and then (and here's the big part) NOT overwrite the data already in the table....just add a new set of data based on what was run?

Thanks for any help! This one has me stumped!!!

 

[FranzV]

Pivot Tables seem to be the perfect solution to get the dynamic tables that can aggregate costs filtered by any combination of the columns you mentioned. If you are not familiar with Pivot Tables, I would suggest this video from Chandoo.

You can also try AutoFilter or Power Query, but I would first try Pivot Tables.

 

[jdlev]

Pivot tables have always been kind of a black art to me...I'm not sure if they'll be powerful enough provide the customization I'm looking for (but like I said...don't know them that well, so could be totally off base). Will pivot tables change if the user inputs change? Here's what I mean...

I have an accounting table I'll call the data table with the following columns:
Unit | Price | Class | Category | Account | Include In Financials?

I'll call my dynamically generated table my results table...the results table to have just 3 of those columns:
Unit | Price | Class

Here's an example of one data set I'd like pulled into the results table from the data table and the logic behind it:
If "Included in financials" = Yes...continue...
If "Price" < 500...continue...
If "Class" = "Expense"...formula returns 'TRUE' then grabs the data Unit | Price | Class from the row and insert in table "Expenses"

Now, I've got a few hurdles I'm trying to overcome...
1) I have no idea what to use to loop through the whole data table to get what I need for my results table rather than referencing a single cell - I'm guessing the 'Power query' you mentioned might be perfect for that?
1B) For that matter, how would I go about allowing the User to add additional rows to the data table that would be included in future searches - like if they added additional Expenses?
2) I'm not sure how to insert multiple rows to the results table after it loops through the data table. So how would I go about building a data table with results 1...x? I was able to use INDEX(range,1,0) to pull a full row of data over, but only based on a single reference...not looping through hundreds of rows...
3) Since the table is dynamic, I'm not sure how to add calculations to the end of it, or maybe just another table that summarizes the data? The calculations don't have to be complex, but I'd like to do stuff like sum, percentages, etc?

 

[jdlev]

Actually, after watching the tutorial video you posted, I think the pivot tables might be able to handle the job after all. Thanks!

 

[FranzV]

I'm glad you are working your way to the solution. I have a few tips after reading the additional details on your second post.

• Format your Data Table using the Format as Table feature. You can do so by selecting a Table Style in the ribbon or by selecting any cell within your data range and clicking CTRL+T. This will make your life a lot easier by:
  • Making formulas easier to write, read, and debug
  • Automatically adding new rows to your calculations
  • Automatically updating the reference range for the Pivot Tables linked to it
  • Expanding formulas to entire columns (including new rows)
• Add helper columns to better filter your data.
  • You could add a column named PriceWithinRange that checks if each row falls within the price range the user typed or selected with a formula that could look something like:
    • =AND( [Price] > = LowerLimit , [Price] < = UpperLimit )
    • Where LowerLimit and UpperLimit are references to the user input cells
  • You could add instead a column named PriceRange to group each row into price ranges defined by you. It could be something like:
    • =LOOKUP( [Price] , PriceCategories )
    • Where PriceCategories is a reference to a range with the upper limits for each category sorted from smallest to largest ( eg. {0,10,50,100,1000})
• Insert Slicers to dynamically filter the data in your Pivot Table.
  • They help in making your report visually appealing
  • Very intuitive for any user, just click on the things you want to see
  • Can easily filter multiple Pivot Tables with only one click
• Enjoy