Question

differentiate between one variable data and two variable data in MS Excel.

Answer 1

hey there
i am vipul here


One Variable Data Table

To create a one variable data table, execute the following steps.

1. Select cell B12 and type =D10 (refer to the total profit cell).

2. Type the different percentages in column A.

3. Select the range A12:B17.

Two Variable Data Table

To create a two variable data table, execute the following steps.

1. Select cell A12 and type =D10 (refer to the total profit cell).

2. Type the different unit profits (highest price) in row 12.

3. Type the different percentages in column A.

4. Select

i hope this answer will be helpfull for u

Answer 2

Now you may want to do an analysis to see what number of monthly payments suits your condition, and how would the monthly payment vary based on it. For example, you may want to keep the tenure of your loan (number of monthly payments) such that your Monthly Payment amount is not more than $1,000.

A quick way is to change the data in this table and the formula would automatically update. However, if you want to test if for many different Number of Monthly Payments, then a One Variable Data Table in Excel would be the way to go.

Setting Up a One Variable Data Table in Excel

Here are the steps to set up a One variable data table in Excel:

In a column, have all the different values of Number of Monthly Payments that you want to test. In this example, we are testing for 72, 84, 96… 240.
Type =B4 in cell E1, which is one row above the values in the adjacent column to the right. This is a construct that needs to be followed when you work with one variable data table in Excel. Also, make sure that the value in cell E1 is dependent on Number of Monthly Payments. It won't work if you manually enter the value in cell E1.
In this case, cell E1 refers to cell B4, which has a value calculated using a formula which uses cells B1, B2 and B3.
Now the data is all set to be used in the One Variable data table.
Select the data (D1:E16). Go to Data Tab –> Data Tools –> What if Analysis –> Data Table.
In the Data Table dialogue box, refer to cell B3 in Column input cell field. Since we are using a one-variable data table, leave the Row input cell empty. Also, we chose Column input cell option as the data is structured in a column.
Click OK. As soon as you click OK, it will automatically fill the cells adjacent to the variable data points (number of monthly payments).
For example, if you wanted to calculated what number of monthly payments you should use to have the monthly payment less than $1,000. You can easily select 144 months (12 years).

Note:

Once you have calculated the values using data table, it can not be undone using Control + Z. You can however manually select all the values and delete it.
You can not delete/modify any one cell in the entire set of calculated values. Since this is an array, you will have to delete all the values.
You can do the same calculation by arranging the data horizontally. The only difference would be that you will have to use Row input cell in the data table dialogue box (instead of column input cell).