Infotron heeft een lijst samengesteld van 25 risico's die regelmatig voorkomen bij het maken van spreadsheets in Excel. Dankzij intensieve samenwerking met onze klanten en het TU Delft Spreadsheet Lab, kan PerfectXL steeds meer van deze risico's automatisch detecteren. Op dit moment controleert PerfectXL op 21 van deze 25 risico's. Lees meer over deze risico check op de website van PerfectXL.
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. Using references is also preferable to using fixed numbers for it is difficult to find all instances of a fixed number.
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).
Exactly the same formula with the same (absolute) references in more than one cell is flagged as a duplicated calculation. It increases the risk of mistakes in future changes or improvements if one forgets to adapt one of both. When a formula contains neither functions (e.g. SUM or PRODUCT) nor operators (e.g. + and *), identical formulas are not considered a duplicated calculation.
Interrupted formula range
It is hard to spot a value amidst a range of formulas.
Only when you look at the formulas does an interruption become apparant.
Usually a range of formulas is uninterrupted, because a column is typically designed to perform all equal types of calculations. When a formula differs from the ones above and below (or right and left), it is considered unusual thus risky.
A range (number of cells > 4) referenced by a function to a range that is larger in the sheet. If the length of a table is shorter than the reference in the formula cell, there is a great chance that the reference is outdated, or simply incorrectly chosen. Note that we currently do not support ranges with a size of 50 cells and over to detect unexpected ranges.
Circle chain of worksheets
Sheet1:B7 uses a value from Sheet3.
Sheet2:B5 uses a value from the first sheet.
Sheet3:D4 uses a value from the second sheet.
A to and fro relationship between two worksheets, or circular references between three or more worksheets. This might cause chaotic workbook structures due to its complexity. Circle chains are usually not necessary.