Is there a function to find the source of #DIV/0! and #NUM! errors in a huge Excel file?

Question

Description of the Excel Worksheet:

I’ve got an economic model with 130 simultaneous equations in Excel. A typical equation looks like the following:

x_t = constant1 * y_t-1

y_t = constant2 * x_t-1 + constant3 * u _t-1

Example function:

=INDEX(tfp_e;1;DS$2)  * INDEX(kc_m;1;DR$2)^INDEX(alfae_e;1;DS$2)  * ((INDEX(lftr_e;1;DS$2)  * (1  - INDEX(utr_e;1;DS$2))  * INDEX(epratio_e;1;DS$2))^(1  - INDEX(alfae_e;1;DS$2)))

• Iteration is allowed in the Worksheet.
• It contains 130.000 cells.
• All the calculations are almost perfect. The average of error is nearly -1.3E-08.

However, in the case of 12 variables, if I just press an ENTER while staying in the cell, then the whole model goes wrong. #DIV/0! and #NUM! errors appears in the cells.

---

My question is that how can I trace back the source of the error? Is there a function to apply in this situation? I think somewhere I missed a bracket or misspelled a range, but I’ve checked all the variables a thousand times.

Answer 1

A great way to narrow down the issue is to use the Evaluate Formula option under Formulas/Formula Auditing.

Press evaluate, if you want to move further in evaluation: this will calculate the currently underlined part.

Press Step in/Step out, if you want to dig deeper into parts of the formula.