Coffee-pot by Matthes // PerfectXL Spreadsheet Validation

Excel Risk Inspections

Calculation Doubts

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’.

PerfectXL Risk Eliminator 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.

Approximate lookup

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.

Approximate Lookup // Calculation Doubts in Excel Files // PerfectXL Risk Eliminator

Double operator

A formula that contains two operator signs 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 Risk Eliminator detects these incidents and lists them under Calculation Doubts.

Double Operator Sign // Calculation Doubts in Excel Files // PerfectXL Risk Eliminator

Fixed numbers

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 Risk Eliminator produces an overview of all the fixed number that occur in your formulas, for you to review.

Fixed Numbers // Calculation Doubts in Excel Files // PerfectXL Risk Eliminator

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.

Second Degree Excel Errors // Calculation Doubts in Excel Files // PerfectXL Risk Eliminator

Standard Excel errors

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).

Second Degree Excel Errors // Calculation Doubts in Excel Files // PerfectXL Risk Eliminator

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 Risk Eliminator detects included subtotals, these incidents are also listed under risk category Calculation Doubts.

Sum includes Subtotals // Calculation Doubts in Excel Files // PerfectXL Risk Eliminator