What-if tables

xlsgen > overview > What-if tables

Creating what-if tables

What-if tables is a feature in Excel for testing values in a formula.

In the screenshot above, we have a regular formula, computing the Total = Number of items * Price of item, symbolized by the formula in cell C5=C2*C3. For testing out the total for different prices (cell C3), we can enter a number of prices in cells B6:B10, a row below the Total formula, then select a cell range around both the formula and the prices, i.e. B5:C10, go in Excel / Data / Table (or Excel / Data ribbon / What-if analyiss / Data table in Excel 2007 or above) in order to create a What-if table. Creating the what-if table results in values automatically calculated for each price, which is what can be seen in cells C6:C10.

What we have described can be achieved programmatically in xlsgen.

Java code

worksheet.putLabel(2,2, "Number of items");
worksheet.putNumber(2,3, 250);
worksheet.putLabel(3,2, "Price");
worksheet.putNumber(3,3, 5);

XlsWhatIfTable table = worksheet.NewWhatIfTable(5,2, 10,3); // B5:C10

table.putFormula("=C2*C3"); // Total

XlsWhatIfTableValues values = table.AddInputValues();

values.SetInputCell(3, 3); // C3 is the value we want to test
values.AddValueFloat(5);
values.AddValueFloat(7);
values.AddValueFloat(9);
values.AddValueFloat(11);
values.AddValueFloat(13);

VB code

worksheet.Label(2,2) = "Number of items"
worksheet.Number(2,3) = 250
worksheet.Label(3,2) = "Price"
worksheet.Number(3,3) = 5

Dim table As IXlsWhatIfTable 
Set table = worksheet.NewWhatIfTable(5,2, 10,3) ' B5:C10

table.Formula = "=C2*C3" ' Total

Dim values As IXlsWhatIfTableValues 
Set values = table.AddInputValues

values.SetInputCell(3, 3) ' C3 is the value we want to test
values.AddValueFloat(5)
values.AddValueFloat(7)
values.AddValueFloat(9)
values.AddValueFloat(11)
values.AddValueFloat(13)

C# code

worksheet.set_Label(2,2, "Number of items");
worksheet.set_Number(2,3, 250);
worksheet.set_Label(3,2, "Price");
worksheet.set_Number(3,3, 5);

IXlsWhatIfTable table = worksheet.NewWhatIfTable(5,2, 10,3); // B5:C10

table.Formula = "=C2*C3"; // Total

IXlsWhatIfTableValues values = table.AddInputValues();

values.SetInputCell(3, 3); // C3 is the value we want to test
values.AddValueFloat(5);
values.AddValueFloat(7);
values.AddValueFloat(9);
values.AddValueFloat(11);
values.AddValueFloat(13);

C++ code

worksheet->Label[2][2]  = L"Number of items";
worksheet->Number[2][3] = 250;
worksheet->Label[3][2]  = L"Price";
worksheet->Number[3][3] = 5;

xlsgen::IXlsWhatIfTablePtr table = worksheet->NewWhatIfTable(5,2, 10,3); // B5:C10

table->Formula = L"=C2*C3"; // Total

xlsgen::IXlsWhatIfTableValuesPtr values = table->AddInputValues();

values->SetInputCell(3, 3); // C3 is the value we want to test
values->AddValueFloat(5);
values->AddValueFloat(7);
values->AddValueFloat(9);
values->AddValueFloat(11);
values->AddValueFloat(13);