Excel does what you ask for, but are you asking the right question? We see many formulas that give us reason to doubt that. For example when fixed numbers or double operators are used. This category of risks we call ‘calculation doubts’.
Calculation Doubts – Risks
PerfectXL zooms in on every formula in your spreadsheet. As soon as it discovers a pattern that indicates that you might have meant something else than what Excel is calculating, the system sends a warning.
When the last argument in a VLOOKUP or HLOOKUP is omitted or set to TRUE, an unexpected match might be returned. When the lookup range is sorted, either an exact or an approximate match is returned. When the range is not sorted, the returned value is always approximate. The returned value is the first “local nearby approach”. But usually just an exact match is desired. PerfectXL detects this approximate lookup and offers you a solution to fix it.
A formula that contains two operators next to each other is often undesired, but very hard to detect. Especially because it yields no Excel error. A classical example is a double hyphen (–), which equates to a plus operator (+), but looks like a minus sign (-) on your screen. PerfectXL detects these incidents and lists them under Calculation Doubts.
A fixed number is a number typed directly in a formula, rather than a reference to another cell containing the number. When numbers change in a later stage, the risk is that one or more numbers are accidentally skipped. VAT-percentages are notorious examples. Using references is also preferable to using fixed numbers, because it is difficult to find all instances of a fixed number. PerfectXL produces an overview of all the fixed number that occur in your formulas, for you to review.
Second Degree Excel Error
Second Degree Excel Errors are standard Excel errors (e.g. #DIV/0! and #NAME?) that result from other Excel errors. Second degree errors are an extra incentive to fix the errors that caused them.
Standard Excel Error
Default Microsoft Excel error messages as a result of formula mistakes is considered a risk, for a dysfunctional formula is usually unintentional. Excel’s errors are #DIV/0! (trying to divide by 0), #N/A! (a formula or a function inside a formula cannot find the referenced data), #NAME? (text in the formula is not recognized), #NULL! (a space was used in formulas that reference multiple ranges), #NUM! (a formula has invalid numeric data for the type of operation), #REF! (a reference is invalid), #VALUE! (the wrong type of operand or function argument is used).
Sum Includes Subtotals
A subtotal/sum that includes both data and subtotals/sums of the same data generally results in a wrong outcome. Some or all numbers are summed up twice accidentally. For example, A3 = A1+A2 and A4 = SUM(A1:A3). A1 and A2 are summed up twice. Possible solutions are to remove subtotals or to consider using a separate column for subtotals. When PerfectXL detects included subtotals, these incidents are also listed under risk category Calculation Doubts.