So, what is an array formula? Well, an formula is a formula that operates on an array of values. For the purpose of this discussion, an array is nothing but a list or collection of values. In this sense, a range, such as A1:A12, is an array.
Another critical point to note is that unlike 'normal' formulas, array formulas are entered by pressing Ctr, Shift and Enter keys simultaneously. Hence the popular appelation of array formulas as 'CSE' formulas. Otherwise, array formulas also begin with '=' and adhere to other normal rules.
An illustration will help shed more light on this topic.
To find out the total number of salespersons who sold more than 5 but less than 40 items, an array formula can be used. Salespersons are in the range A2:A7 and the sold items in the range B2:B7. Select an empty cell, type =SUM((B2:B8>5)*(B2:B5<40)) into it and press Ctrl, Shift and Enter keys simultaneously. The Excel automatically places the formula within braces to signify it as an array formula. The cell where the formula was entered displays the total number of salespersons who sold more than 5 but less than 40 items, which is 4.
This is what happens. The formula compares values contained in B1 against 5. If the value is greater than 5, TRUE is returned. Else the returned value is FALSE. Similarly, the value contained in B2 is compared against 40. If it is less than 40, then TRUE is returned. Else, FALSE is returned. Now, the two returned values are multiplied. As Excel treats TRUE as 1 and FALSE as 0, the multiplication results in either 1 or 0. This procedure is repeated for all the cells, producing a series of 1's and 0's, which are totaled by the function SUM, giving the total number of salespersons who sold more than 5 but less than 40 items.
No comments:
Post a Comment