Meccano by Hornby // PerfectXL Spreadsheet Validation

PerfectXL Risk Eliminator

Array functions are past times

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them. This is perhaps because of the very different nature of the formulas or the strange way of activating them (CTRL-SHIFT ENTER instead of just ENTER).

What is an array function?

An array formula is, simply stated, a formula that very cleverly combines multiple calculations as an array. For example, SUM (B2: B10 * C2: C10) is the sum of (B2 * C2) + (B3 * C3) etcetera. The result of the array function will only be shown after you double click the cell with the formula to select it and then press CTRL+SHIFT+ENTER.

Array function example // Array functions are past times // PerfectXL

The danger of using array functions

It’s better not to use array functions unless it’s truly necessary because, your spreadsheet might later be maintained by someone who does not understand array functions very well. They might change something in the array formula without knowing that it is an array…so they won’t press CTRL-Shift-Enter and your nice functionality will be completely lost and gone. Or they might make a change in the input column of the array without realizing the consequences and everything goes wrong.

Array functions are past times // PerfectXL Risk Eliminator

Alternatives

Fortunately, there are alternative methods to get to the same results. For example, use an extra column for an intermediate calculation (B2 * C2, as in the example). There is rarely a lack of space since Excel 2010. You can also choose to use new smart built-in Excel features like SUMIFS, MAXIF, and AVERAGEIFS, which can also be combined to attain similar results as those that come with an Array function.

Array functions - Alternative // PerfectXL Risk Eliminator

When to use array functions

When is it safe to use array functions? If you’re sure nobody else ever has to worry about it and/or you are the only user of the spreadsheet (but how do you know?). They can also be used in situations where the spreadsheet’s memory usage needs to be kept very limited, because array formulas are memory efficient.

How does PerfectXL help?

If you analyze your spreadsheet with PerfectXL Risk Eliminator and go to the risk overview, you will see a section called, “complex formulas.” This shows formulas which are complex and therefore difficult to maintain and understand, and potentially risky for future users. In this category you are able to see whether or not your spreadsheet contains array functions, and where those array functions are.

  • Avoid >70 types of risks
  • Improvement suggestions
  • Quality reports

Read more about risks in Excel files

On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.

Here you will find information like system requirements, release notes, and answers to most questions about our software.

VLOOKUP and the combination of INDEX and MATCH are well known ways to search in Excel, and to our great frustration, VLOOKUP is used a lot more often.

Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.

Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.

(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references.

Whenever possible, keep your Excel formulas short and simple. Avoid long formulas; they are harder to read, harder to understand, difficult to check and tough to improve.

The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.

Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.

Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Make sure that you calculate your formulas only once!

It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!

It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.

Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.

To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.

Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.

Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.

Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.

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.

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.