Evaluate Formulas in Excel
How to use xlCompare to debug calculations in your Excel workbook
Imagine, you have the following formula in one of the cells on your worksheet:
The question is, what are the arguments of the SUMPRODUCT function?
Which values were produced by VALUE function and passed to the SUMPRODUCT?
One of the quick methods:
- Use Evaluate Formula command on the Formulas tab
- Evaluate several sub expressions and get the output of the VALUE function
How to use xlCompare to Evaluate Formula in Excel File
- Open your file in xlCompare
- Select the formula you would like to debug
- Use Tools | Evaluate Formula command (or the same command in the Right Click Menu)
xlCompare brings you are window with calculation tree with all subexpressions in your formula.
Expand its nodes until you get the VALUE node.
Array returned by the VALUE function is marked as RETURN VALUE icon and can be expanded.
In this case we have vector (single row) of 3 values.
Are there any other features I can use for debugging?
In case if argument of your formula is cell with other formula, xlCompare allows you to drill down into the argument and expand its calculation tree until you get to the input values.
In the example below we have formula
You can expand every of the cells D2, E2, F2
How to Trace Precedents in Excel worksheet?
Please, visit this article: