Excel What-If Data Tables: Faster calculation with VBA

For some reason this year I keep on meeting Excel’s What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest rates. What-If Data Tables are a great tool but they tend to calculate very slowly, in fact more slowly than I think they should.

Using What-If Data Tables

What-If Data Tables can be either 1-dimensional or 2-dimensional. You create them using the What-If pull-down on the Data tab.

First you need to create the outline for the Data Table:

This is a 2-dimensional data table. The idea is that you have a single formula (in cell E7) that shows the results of a set of calculations that are dependent on 2 constants (in F3 and G3). We want to recalculate the workbook for every combination of the values in the range (row) F7:H7 and the column E8:E12, and place the corresponding result in F87:H12.

Start by selecting the range E7:H12 and click the What-If Data Table button:

You are asked to pick the Row Input Cell and Column Input Cell, and when you click OK the range F8:H12 gets filled with a TABLE array function formula {=TABLE(F3,G3)}

Note that for some strange reason Excel requires the row input and column input cells to be on the same worksheet as the data table.

What-If data tables calculate slowly because Excel has to recalculate many times (in this case 3 x 5 = 15 times. So you need to be in Manual calculation mode, or possibly in Automatic except Data Tables mode (this makes calculation automatic but does not do the iterative calculation of the data table(s)).

Speeding Up What-If Data Table Calculation.

I did some experiments to see if the data table calculation could be speeded up and found:

• Each iteration is calculated using single-threaded calculation.
• The process starts with a multi-threaded recalculation, then iterates using single-threaded recalculation, then finishes with an additional recalculation.
• There seems to be quite a large overhead to this overall process.
• If the one combination of values in the table is the same as the initial set of values it gets recalculated anyway even if not needed.

The VBA Solution

So I wrote some VBA to see if by eliminating these problems I could make it run faster.
Note that the VBA is written as a proof-of-concept demonstration rather than production level code.

Sub IterateTables()
'
' Proof-of-Concept code
' for faster calculation of a 2-D What-If data table
' Copyright Charles Williams, Decision Models 16-December-2015
'
Dim rngTable As Range
Dim rngRowCell As Range
Dim rngColCell As Range
Dim varRowSet As Variant
Dim varColSet As Variant
Dim varResults() As Variant
Dim rngFormula As Range
Dim nRows As Long
Dim nCols As Long
Dim lCalcMode As Long
Dim j As Long
Dim k As Long
Dim varStartRowVal As Variant
Dim varStartColVal As Variant
Dim varFirstVal As Variant
Dim blCalculated As Boolean
Dim dTime As Double
'
' get the what-if table and its formula
'
Set rngTable = ActiveCell.CurrentRegion    ' expand active cell to the current region
'
Set rngFormula = rngTable.Cells(1, 1)   ' Top-Left corner
nRows = rngTable.Rows.Count - 1         ' number of rows in the Column of what-if values
nCols = rngTable.Columns.Count - 1      ' number of columns in the row of what-if values
'
' get the row and column input cells using RefEdit
'
With ufIterTable
.RefEditRow.Value = ""
.RefEditCol.Value = ""
.Show
If ufIterTable.RefEditRow.Value <> "" Then Set rngRowCell = Range(.RefEditRow.Value)
If ufIterTable.RefEditCol.Value <> "" Then Set rngColCell = Range(.RefEditCol.Value)
End With
'
' if 2-D and we have got the row and column cells then proceed
'
If nRows > 0 And nCols > 0 And Not rngRowCell Is Nothing And Not rngColCell Is Nothing Then
dTime = Microtimer
'
' create output results array
'
ReDim varResults(1 To nRows, 1 To nCols)
'
' get row and column arrays of what-if values
'
varRowSet = rngFormula.Offset(0, 1).Resize(1, nCols).Value2
varColSet = rngFormula.Offset(1, 0).Resize(nRows, 1).Value2
'
' set environment
'
Application.ScreenUpdating = False
lCalcMode = Application.Calculation
If Application.Calculation <> xlCalculationManual Then Application.Calculation = xlCalculationManual
'
' can only skip initial values if workbook is calculated at start
'
If Application.CalculationState = xlDone Then
blCalculated = True
Else
blCalculated = False
End If
'
' initial start values
'
varStartRowVal = rngRowCell.Value2
varStartColVal = rngColCell.Value2
varFirstVal = rngFormula.Value2
'
' calculate result for each what-if values pair
'
For j = 1 To nRows
For k = 1 To nCols
If blCalculated And varRowSet(1, k) = varStartRowVal And varColSet(j, 1) = varStartColVal Then
'
' if whatis value pair is the same as the start values then skip recalc
'
varResults(j, k) = varFirstVal
Else
'
' show calc status in the status bar
'
Application.StatusBar = "What-If Table Row " & j & " Column " & k
'
' set values for this iteration, recalc, store result
'
rngRowCell.Value2 = varRowSet(1, k)
rngColCell.Value2 = varColSet(j, 1)
Application.Calculate
varResults(j, k) = rngFormula.Value2
End If
Next k
Next j
'
' reset status bar
'
Application.StatusBar = False
'
' put results back
'
rngFormula.Offset(1, 1).Resize(nRows, nCols) = varResults
'
' reset back to initial values & recalc
'
rngRowCell.Value2 = varStartRowVal
rngColCell.Value2 = varStartColVal
Application.Calculation = lCalcMode
Application.Calculate
'
' timer message
'
dTime = Int((Microtimer - dTime) * 1000) / 1000
MsgBox "Time for " & nRows * nCols & " Iterations: " & dTime & " Seconds"
End If
End Sub

Timing Results

I ran some timing tests using a 4-core I7 with hyper-threading so that Excel tries to use 8 cores for multi-threaded calculation.

The first row gives the time for a single calculation in multi-threaded and single-threaded mode.
The second row gives the time to calculate using Excel’s What-If data table method. You can see that difference between single-threaded and multi-threaded is equal to 2*(Single-thread time – multi-thread time).
The third row gives the time to calculate using my VBA routine: a lot faster in multi-threading mode and slightly faster in single-threading mode.

A timing breakdown for XL Data Tables could be:
2 * multi-threaded Calc + 15 * single-threaded Calc + overhead
2*0.65 + 15 * 2.8 + 0.3 = 43.6

And for the VBA:
15 * multi-threaded calc + overhead
15 * 0.65 + 0.15 = 9.9

Conclusion

You can download the test workbook from

https://onedrive.live.com/redir?resid=B4A055A3ADB4DC8C!2144&authkey=!AIvdcj16DC4AwoU&ithint=file%2cxlsm

I have made a suggestion in Excel User Voice to use these findings to speed up Excel’s What-If Data Tables. Please vote for it if you think it has merit:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/11097150-what-if-data-table-calc-should-use-multi-threading

Also it would be great if existing data table users could try out my VBA code to see how it works for them.

I plan to build a more generalised solution into FastExcel Calc.

http://www.decisionmodels.com/fastexcelV3SpeedTools_Calc.htm

 

 

Advertisements