It’s hard to analyze and show three variables in two dimensional space with a Data Table, but Excel Video 343 has some ideas on how you might do it. The first way to analyze three variables is to simply change the third variable and watch as the Data Table changes.
If you want something more like a Pivot Table to display the three variables, you’ll see how I created three Data Tables and then wrote formulas to copy specific rows from the three Data Tables. I’ll show you how I redid the Data Table to point to new cells as part of the video. Also note that I had to change the payment formulas in cells B43 and B67 to reference the new variable amounts. I redid the Data Table for a $325,000 loan amount as part of the video, but if you watch carefully, I needed to recalculate the $300,000 Data Table as well. I fix that and then resume recording.
The idea is that you can have more than two variables in a Data Table. If you want your spreadsheet to be formatted like a Pivot Table, you need to copy and paste the Data Table as many times as your variables change, change the Data Table formula to reference the new variable cells, and then recreate the Data Table. There are limitations to what you can and can’t do with data in a Data Table. That’s what we’ll discuss in Excel Video 344. I look forward to seeing you then.
