How To Use Excel What If Analysis Data Table

Select the range f2 l13.

How to use excel what if analysis data table. For example you can do what if analysis to build two budgets that each assumes a certain level of revenue. 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. Now you will get the instalment amount till selected range of columns. On the data tab in the forecast group click what if analysis. For the row input cell select the term input c4.

You can use excel s goal seek feature to find the answer. 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. And the data table analysis is one of them. Click what if analysis in the data tools group. Click on the what if analysis option and select data table option.

Once selected click on the data then what if analysis from that dropdown select data table. Once select data table the below pop up will come. Or you can specify a result that you want a formula to produce and then determine what sets of values will produce that result. Under the data tools section press the what if analysis button. Now select the entire table to apply data table of what if analysis as shown in the below screenshot.

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. On the data tab in the forecast group click what if analysis. This is extremely useful to analyze a problem in excel and figure out the best solution. Now type a 2 in the column input cell box and click on the ok button.

For the column input cell select the interest rate c3. It will display data table dialogue box.