What-if Analysis and Data Tables in Excel

Once of the very powerful features of Excel is the automatic recalculation of results of a formula if the data is changed. This feature is known as What-if analysis.

To understand the above statements let us take an example of Results of Class X worksheet. Open the Results of Class X worksheet. Suppose you ask a question- what-if I increase the marks of Peter from 79 to 80 in English. Will his average cross 80 %?

To answer this question, simply change Peter’s marks in English from 79 to 80.

You will notice that all the formulae take note of this change and recalculate the results. The total moves up by one from 399 to 400 and the average becomes 80 %. Your question has been answered easily by changing the marks in the spreadsheet.

Data Tables:

Tables, also known as Data tables, are extensively used in Excel to perform what-if analysis. Using a data table, you could show how the results vary by changing certain values in a formula.

A data is a collection of cells which shows how changing some values in your formulas that affect the results.  Data tables provide a shortcut for calculating multiple results in one operation. They offer a way to view and compare the results of different differences together on your worksheet.

There are two types of data tables.

• One variable data table
• Two variable data table

 In a one variable data table, you change only one variable to see its effect on the result. In a two variable data table, you change two variables at the same time to see its effect on the result.

Questionnaire:

• Explain about What-if Analysis?  
• What is Data Tables? What are the types of data tables? Explain.