CODEWITHSUNDEEP
excelexcel-formula
athos
athos

Reputation: 6425

Is there a way to record down the sequence of Excel formula calculation

When press F9 or call Calculate in VBA, formulas in Excel get executed. However, the sequence is done by Excel itself, up to the dependency constraints in formula dependency. Is there a way to record down the calculation process?

for example,

I hope there's a way to show that calculation sequence is - 0 for cells A1 and A2 - 1 for cells A3 and A4 - 2 for cell A5

Upvotes: 0

Views: 195

Answers (1)

Charles Williams
Charles Williams

Reputation: 23550

To determine the last calculation sequence used you could extract the calculation chain part from the XLSX/XLSM file and decode it. See https://msdn.microsoft.com/en-us/library/office/gg278336.aspx as a starting point

For small scale tests you can use a Calculation Sequence tracking UDF like this one
http://www.decisionmodels.com/Downloads/CalcTrace.zip

But note that the calculation sequence dynamically changes at each calculation. To use CalcTrace unzip the XLA and unblock it (right-click the XLA->Properties->Unblock). Then open the XLA as if it was a workbook. For each formula you want to see the calculation sequence for (for example A4 and A5 add a formula alongside =CalcSeqCountRef(A4) and =CalcSeqCountRef(A5). This returns a count that is incremented by 1 each time CalcSeqCountRef is calculated. Since CalcSeqCountRef(A4) is dependent on A4 it is calculated each time A4 is calculated.

Upvotes: 2

Related Questions