To find out, why formula produces wrong result, and which part of formula must be changed, you should analyze each part of formula.
Evaluate Formula command shows formula divided into subexpressions, and evaluate each of them.
You can expand this tree to go in-depth and analyze all expressions. Each argument may be expanded and evaluated recursively.
Dependency Auditor also shows results of OFFSET and INDIRECT functions in this window, if these functions are used in formula.
So, you can quickly find real source of this problem.