How To Use Excel What If Analysis Data Table

And the data table analysis is one of them.

How to use excel what if analysis data table. This is extremely useful to analyze a problem in excel and figure out the best solution. Select the range f2 l13. For the column input cell select the interest rate c3. The data table is perfect for that situation. Do the analysis with the what if analysis tool data table select the range of cells that contains the formula and the two sets of values that you want to substitute i e.

You can use excel s goal seek feature to find the answer. Now type a 2 in the column input cell box and click on the ok button. 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. On the data tab in the forecast group click what if analysis. Sometimes a formula depends on multiple inputs and you d like to see how different inputs values would impact the result.

Excel provides several different tools to help you perform the type of analysis that fits your needs. Or you can specify a result that you want a formula to produce and then determine what sets of values will produce that result. Click on the what if analysis option and select data table option. It will display data table dialogue box. Once select data table the below pop up will come.

Now select the entire table to apply data table of what if analysis as shown in the below screenshot. For the row input cell select the term input c4. Under the data tools section press the what if analysis button. Select data table from the drop down menu. Now you will get the instalment amount till selected range of columns.

For example you can do what if analysis to build two budgets that each assumes a certain level of revenue. On the data tab in the forecast group click what if analysis.