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 hard coded 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

Hard coded numbers

A hard coded 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 hard coded number. PerfectXL Risk Eliminator produces an overview of all the hard coded 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

Risk inspection categories

Below you’ll find an overview of the risk categories that PerfectXL Risk Eliminator detects. Click a category to read more.

Hiding “unimportant information” from other users may seem like a good idea to make complex spreadsheets more user friendly. But in reality that’s not the case. Hidden information can be altered by a user, without him even realizing what he’s doing, resulting in potential negative results.

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 hard coded numbers or double operators are used. This category of risks we call ‘calculation doubts’.

Larger spreadsheets are usually stuffed with formulas based on cell ranges. When spreadsheets are edited or expanded over time, different types of range issues may occur. These issues are usually hard to detect, but they can be very risky! We’ve seen big numbers and large sums of money ‘disappear’ because of range issues.

Currently Excel offers about 475 standard functions and new ones are added regularly. Most of these functions are rock-solid, but a couple of them we consider vulnerable functions. Some of which are frequently used by many Excel professionals. We suggest you try to avoid using them. Excel offers a range of fine alternatives.

The creation of a good structure is one of the most important, yet most underestimated aspects in the development of a spreadsheet. Usually a spreadsheet starts small, but then it grows and nobody gets around to reorganizing the structure anymore. PerfectXL Risk Eliminator groups the risks resulting from ill structure under ‘structure issues’.

The art of programming is the art of keeping things simple. Complex formulas are hardly ever wise to use. Calculations can usually be simplified. You just have to figure out how. PerfectXL points out different types of complex formulas in your spreadsheet and it offers suggestions to improve the complexity.

Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files. PerfectXL checks your macro-riddled spreadsheets for potential problems in the VBA code.