One way Data Table - Excel

I would like to tell about one great feature in excel – the Data table.

Let us consider the following simple example to illustrate this feature.

We have Principal, Rate of interest and Period as input values.

Now let’s calculate Simple interest using them. We do so by using the formula: =Principal * ROI*Period. We can calculate Amount using the formula =Principal +  Simple interest.

Note : I have given Range names for the cells which contain the values of Principal, rate of interest and period.

We get the output values as:

Now let’s say suppose we would like to calculate the Simple interest and Amount for varying interest rates from 1% to 25%. One easy way to do this would be to use the Data table. To use that feature we need to put the range of interest rates in one column.  Have the column headers as ROI, Simple_Interest and Amount. Note that the row above the first value of rate of interest is left empty. We will see why it is so.

Our interest is to calculate the Simple interest and Amount for varying interest rates. To use the data table feature, we need to give a model(example) calculation for the Simple interest and Amount. That’s the reason we left an empty row above the first value (below the column headers). We give the model calculation just by referencing the cell which has the actual value of Simple interest that we just calculated using a formula.

We do the same for Amount.

Now we select the entire range of cells including the cells which has the model calculation.

Click Data tab.

Click What- If Analysis

 Click Data Table

In the Column input cell we need to refer the cell which has the rate of interest value that was used in the original formula that we used at first to calculate the Simple interest. We give it in the Column input cell text box because the input values which are varying are in the same column.

Now click OK and here it is. You just computed around 50 values in no time.