Windows 7

# Microsoft Excel 2010 : Calculating the Mode (part 3) - Getting the Mode of Categories with a Formula - Accommodating a Function’s Arguments

2/6/2014 3:16:43 AM
##### Recapping the Array Formula To review how the array formula counts the values for the modal category of Ford, consider the following: The formula’s purpose is to count the number of instances of the modal category, Ford, whose name is in cell C1.The innermost fragment in the formula, A2:A21=C1, returns an array of 20 TRUE or FALSE values, depending on whether each of the 20 cells in A2:A21 contains the same value as is found in cell C1.The IF() function examines the TRUE/FALSE array and returns another array that contains 1’s where the TRUE/FALSE array contains TRUE, and 0’s where the TRUE/FALSE array contains FALSE.The SUM() function totals the values in the array of 1’s and 0’s. The result is the number of cells in A2:A21 that contain the value in cell C1, which is the modal value for A2:A21. Using an Array Formula Various reasons exist for using array formulas in Excel. Two of the most typical reasons are to support a function that requires it and to enable a function to work on more than just one value. Accommodating a Function One reason you might need to use an array formula is that you’re employing a function that must be array-entered if it is to return results properly. For example, the FREQUENCY() function, which counts the number of values between a lower bound and an upper bound requires that you enter it in an array formula. Another function that requires array-entry is the LINEST() function. Both FREQUENCY() and LINEST(), along with a number of other functions, return an array of values to the worksheet. You need to accommodate that array. To do so, begin by selecting a range of cells that has the number of rows and columns needed to show the function’s results. (Knowing how many rows and columns to select depends on your knowledge of the function and your experience with it.) Then you enter the formula that calls the function by means of Ctrl+Shift+Enter instead of simply Enter; again, this sequence is called array entering the formula. Accommodating a Function’s Arguments Sometimes you use an array formula because it employs a function that usually takes a single value as an argument, but you want to supply it with an array of values. The example in cell C2 of Figure 2 shows the IF() function, which usually expects a single condition as its first argument, accepting an array of TRUE and FALSE values as its first argument: =SUM(IF(A2:A21=C1,1,0)) Typically, the IF() function deals with only one value as its first argument. For example, suppose you want cell C2 to show the value “Current” if cell A1 contains the value 2010; otherwise, B1 should show the value “Past”. You could put this formula in B1, entered normally with the Enter key: =IF(A1=2010,“Current”,“Past”) You can enter that formula normally, via the Enter key, because you’re handing off just one value, 2010, to IF() as its first argument. However, the example concerning the number of instances of the mode value is this: =SUM(IF(A2:A21=C1,1,0)) The first argument to IF() in this case is an array of TRUE and FALSE values. To signal Excel that you are supplying an array rather than a single value as the first argument to IF(), you enter the formula using Ctrl+Shift+Enter, instead of the Enter key alone as you usually would for a normal Excel formula or value. Looking Inside a Formula Excel has a couple of tools that come in handy from time to time when a formula isn’t working exactly as you expect—or when you’re just interested in peeking inside to see what’s going on. In each case you can pull out a fragment of a formula to see what it does, in isolation from the remainder of the formula.
 Other -----------------

 Most view of day
 Top 10