How to test whether a single-cell range is a dependent (first-order, second-order, ... n-th order) of a given range (single cell or multiple)?

[the_arcadian]

Hello MrExcel gurus,

The title of this post has the jist of my question, but here's a bit of background: I'm trying to optimize a very bulky financial model. I've got calculation time down to a couple seconds or so, but I'd like to isolate the specific cells whose calculations are still slowing things down. (Note: I know intuitively which formulas are slow... I just need to show it empirically to convince my team that changes are beneficial...)

I understand (per the discussion here) that by default Excel runs a 'smart calculation' algorithm upon workbook opening. After the 'smart calculation' determines the workbook's dependency trees, then if changes are made to the workbook, Excel only recalculates those dependencies impacted by that change (per here).

In that vein, could someone help me with the following:
Given a change to a specific range (one cell or multiple), call it "Range_X," I want to loop through each used cell in every worksheet and (1) determine if the cell is a dependent of Range_X and, if so, (2) evaluate the calculation time of that cell.

I have code to accomplish (2), but I'm at a loss for how to test (1), especially since I need to test whether each cell is a dependent of any order (i.e., a 'first order' dependency is a direct cell reference to Range_X; a 'second order' dependency is a reference to another cell that itself references Range_X, etc.).

Hope you all find this one interesting. Would appreciate any thoughts! Thanks!

 

[Oaktree]

Welcome to the board.

I'm sure there's a way to do that; but, even if you were to work that out, I don't think it's going to be as enlightening as you hope/expect it to be.

Reason being: the number of dependencies is far less important than the efficiencies of the formulas that reference those dependencies. As a trivial example, if you have the formula =$A$1 copied to 100,000 cells, that won't bog down your spreadsheet nearly as much as e.g. 10,000 VLOOKUPs that reference a range that includes A1.

As such, I'd focus on the juicy formulas instead.

As a fairly common example, replacing VLOOKUP with INDEX/MATCH can have a significant impact on recalc time (INDEX/MATCH is never slower than VLOOKUP, and it's usually faster). Replacing *either* of those with a code-based solution (load data to arrays in VBA, create a dictionary object, merge your dictionary against the "lookup" array in VBA, and paste the values) can have a massive impact on recalc time. I've seen that approach take recalc times down from literally hours to mere seconds.

 

[DRSteele]

Welcome to the forum.

While I have no answer to your specific question about determining calculation duration, I do have suggestion: upgrade your software. Excel365 has a new calculation engine (as well as several new functions and dynamic arrays), so the whole discussion might become moot. Testimony from most users of Excel365's new engine reflects the vast increase in speed, power and capabilities.

The web is full of discussion and youtube has many videos.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.accountingweb.com/technology/excel/why-excel-2019-is-already-obsolete[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.youtube.com/watch?v=ZXn0D4Ch7zg[/FONT]

 

[the_arcadian]

Thanks to both.

Maybe as an alternative to testing for all dependencies, as this seem like it'd be very slow, is there a way to evaluate whether a single-cell is recalculated when another cell (our Range_X) is changed? That would inherently tell me whether the two cells are lined (regardless of the number of dependencies).

Thanks!