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

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.

Calculation Doubts :: Risk Detection in Spreadsheets :: PerfectXL

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 :: Risk Detection :: PerfectXL

Double Operator

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.

Double Operator Signs :: Calculation Doubts :: Risk Detection :: PerfectXL

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

Fixed Numbers :: Calculation Doubts :: Risk Detection :: PerfectXL

Read our guideline about the use of fixed numbers in spreadsheets

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 :: Risk Detection :: PerfectXL

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

Standard Excel Errors :: Calculation Doubts :: Risk Detection :: PerfectXL

Read our guideline about neglecting Excel errors

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.

Sum includes Subtotal :: Calculation Doubts :: Risk Detection :: PerfectXL