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

jdlev

New Member
Joined
Jan 6, 2017
Messages
27
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!!!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
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.
 
Last edited:

jdlev

New Member
Joined
Jan 6, 2017
Messages
27
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

New Member
Joined
Jan 6, 2017
Messages
27
Actually, after watching the tutorial video you posted, I think the pivot tables might be able to handle the job after all. Thanks! :)
 

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,395
Members
414,063
Latest member
N_Bates

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