It consists of 6 input variables and a simple cost and revenue model to produce a profit. This is a fictitious mine but provides a simple model which we can use the data Table function to analyse. … and this simple dialog box is all empowering ?įor demonstration of the Data Table function I am going to use a simple profit model of a Gold Mine, “The Blue Sky Mine Co”. In Excel up to 2003 go to the Data Menu and select Table…īoth Excel 97-/10 then bring up the same Data Table dialog box. In Excel 2007 & 2010 go to the Data Tab, What If Analysis panel and select Data Table The Data Table function is hidden away in different locations within different versions of Excel but apart from the menu location the functionality is the same throughout. The Data Table function should not be confused with the Insert Table function. The Data Table function is a function that allows a table of what if questions to be posed and answered simply, and is useful in simple what if questions, sensitivity analysis, variance analysis and even Monte Carlo (Stochastic) analysis of real life model within Excel. This post is going to detail the use of the Data Table function within Excel, which can help you answer that question.
How often have you thought “ I’d like to know what our profit would be for a +/- 10, 20 and 30 % variance in the costs” ? This post is going to detail the use of the Data Table function within Excel, which can help you answer those questions and then so so much more. How often have you thought of or been asked “I’d like to know what our profit would be for a number of values of an input variable” or “Can I have a graph of Profit vs Cost vs …” If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only thing missing is the Spandex Pants and Red Cape! Please note that this post is unusually large by standards. This is a Guest Post by Hui, an Excel Ninja and One of the Moderators of our Forums.