Usage of Array Functions

In my previous post I explained the concept of Array functions (or formulas - please note that I will be using these two words interchangeably). Now, I will take up different ways in which Array functions can be used.

As, we know an array is a collection of data points, not one single data point. Now you can do any processs an array in two ways - either to get a single data point result or a group of data points as result after processing the array (processing array means = doing some calculations, or any kind of processing).

Here are a few examples:

1. Calculating average of a series based on multiple conditions, where the conditions are applicable on mupltiple data columns
2. Calculating count of a column with one or more conditions applicable on column other than the one on which count is to be performed

Though above examples can be solved using functions such as - SumIF(), AverageIF(), CountIF(), etc., but if the above examples get a little complex, then array functions are the best to solve.

Before you can start writing array functions, I would like to list down some advantages of using them. Here are a few advantages that array functions have over other functions:

• The most importance advantage of array functions, especially over functions like SumIF(), CountIF() etc., is that they can be used to perform calculations based on complex conditions
• Array functions when used over a range (and in multiple cells) would prevent any changes which happen by mistake, as a set of functions if input as an array, cannot be deleted/edited unless all cells within the array range are deleted
• These functions also ensure consistency. As array function requires the ranges in the function to be of same length, it ensures that the arguments provided with the function are correct and are not of unequal lengths

Though array functions can solve most of your complex calculations which otherwise MAY not be possible using other functions, they also come with certain restrictions/limitations that make them not so very helpful in certain situations. Here are some disadvantages associated with array functionsl:

• Array functions are processed with each cell (or cell combination) processed individually. This requires system to perform same calculation over again for the all the cells mentioned in the range. This leads the system to take that extra time to calculate all array ranges again.
  
  In case there are many array functions used in a worksheet, it can lead to re-calculations taking lot of time.
• Array functions cannot be worked on whole columns or whole rows. I.e., you cannot use range names such as - A:A or B:B or C:C, etc.
• These functions are a bit difficult to understand and implement. If they are not properly worked with, they can result in results which are not accurate, and it would be difficult for one to understand what is leading to inaccurate results

Additionally, whatever that can be performed through array functions, most of it can be performed using normal functions. But certainly, the ones which can be solved using only array functions, they are best performed using array functions only.

In my next post, I will take a few more examples, simple and complex, to explain how can one use array functions.

Share on Facebook Share on Twitter Share on Google Plus

About Dhakkanz

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.