How to Create a Data Table with 3 Variables

Example 1 – Using a PivotTable to Create a Data Table with 3 Variables

Enter Principle, Months, and Rates in B5:D12.

writing combinations of parameters to create a data table with 3 variables

Click E5 and enter the following formula.

=PMT(D5,C5,B5)

Press Enter.

This is the output.

writing PMT formula to create a data table with 3 variables

Drag down the Fill Handle to autofill the formula.

Select B5:E12.
Go to the Insert tab.
Select PivotTable.

inserting pivot table to create a data table with 3 variables

Select B5:E12 in Table /Range.
Select New Worksheet.
Click OK.

opening new worksheet with pivot table to create a data table with 3 variables

In the new worksheet, drag Principle to Columns.

adding field to columns area to create a data table with 3 variables

Drag Months and Rate to Rows.

adding fields to rows area to create a data table with 3 variables

Drag PMT to Values.

The pivot table is created.

adding field to values area to create a data table with 3 variables

Click a cell in the pivot table.
Go to the Design tab.
Select Report Layout tab.
Choose Show in Tabular Form.

showing pivottable in tabular form to create a data table with 3 variables

Click a cell in the pivot table.
Go to the Design tab.
Select Off for Rows and Columns in Grand Totals.

Go to the Design tab.
In Subtotal, select Do Not Show Subtotals.

removing subtotals to create a data table with 3 variables

This is the output: a data table with 3 variables.

Example 2 – Using the Data Table Command

Steps:

Select C15 and enter the formula below.

=SUMPRODUCT(C12,C13,C14)

Press Enter.

This is the output.

inserting sumproduct function to create a data table with 3 variables

Click D5 and enter:

=C12

Press Enter.

Select E5 and enter the formula below.

=D5+5

Press Enter.

Drag down the Fill Handle to see the result in the rest of the cells.

Choose C6 and enter the following formula.

=C13

Press Enter.

Enter the following formula in C7.

=C6+0.5

Press Enter.

Drag down the Fill Handle to see the result in the rest of the cells.

Choose C5 cell and enter:

=C15

Press Enter.

Select C5:H10.
Go to the Data tab.
Select Data Table in What-If Analysis.

using data table command to create a data table with 3 variables

Select C13 as Row input cell and C12 as Column input cell.
Click OK.

Enter the list with the number of sellers in G13:G18.

Select E13 and go to the Data tab.
In Data Tools, select Data Validation.

inserting data validation command to create a data table with 3 variables

Select List in Allow.
Select G13:G18 as Source.
Click OK.

Select C14 and enter:

=E13

Press Enter.

The value of E13 in the drop-down list changes and the values within the data table will also change.

How to Create a Data Table with 3 Variables – 2 Examples

Example 1 – Using a PivotTable to Create a Data Table with 3 Variables

Example 2 – Using the Data Table Command

Related Articles

Leave a reply Cancel reply