Most of Excel's built in functions are formulas that calculate a single value (like a sum) and put that value into a worksheet cell. Excel has another type of function. It's called an array function because it calculates multiple values and puts those values into an array of cells, rather than a single cell.
FREQUENCY is a good example of an array function. It's job is to summarize a group of scores by showing how the scores fall into a set of intervals that you specify. For example, given these scores: 77, 45, 44, 61, 52, 53, 68, 55 and these intervals: 50, 60, 70, 80. Frequency shows how many are less than or equal to 50 (2 in this example), how many are greater than 50 and less than or equal to 60 (that would be 3), and so on. The number of scores in each interval is called a frequency. A table of the intervals and frequencies is called a frequency distribution.
**It's very important to press Ctrl+Shift+Enter to close the Function Arguments dialog box and put the values in the selected array.
Defining names for arrays of cells:
As you get more into Excel's statistical features, you work increasingly with formulas that have multiple arguments. Oftentimes, these arguments refer to arrays of cells. If you apply meaningful names to these arrays, it helps you keep straight what your'e doing.
Excel gives an easy way to attach a name to a group of cells:
1. Put a descriptive name at the top of a column (or to the left of a row) you want to name.
2. Select the range of cells you want to name.
3. Right-click on the selected range.
4. From the pop-up menu, select Define Name
5. Click OK
Four rules to follow from defining names are:
1. Begin a name with an alphabetic character
2. Make sure that the name contains no spaces or symbols. Use an underscore to denote a space.
3. Be sure the name is unique within the worksheet
4. Be sure the name doesn't duplicate any cell reference in the worksheet.
After defining a name for an array of cells, you have the convenience of using the name in a formula.
To keep track of names in a worksheet, select Formulas | Name Manager
A couple of formulas that show just how convenient this naming capability is are SUMIF and SUMIFS. These formulas add a set of numbers if specific conditions in one cell range (SUMIF) or in more than one cell range (SUMIFS) are met.
For example:
To sum the data in Column C, but only for the North region. That is, to only consider a cell in column C if the corresponding cell in column B contains "North".
1. Select a cell for the formula result
2. Select Formulas | Math & Trig | SUMIF, SUMIF has three arguments. The first, RANGE, is the range of cells to evaluate for the condition to include in the sum (North, South, East, or West in this example). The second, Criteria, is the specific value in the Range (North, for this example). The third, Sum_range, holds the value I sum.
3. In the Function Arguments dialog box, enter the appropriate values for the arguments.
Here's where the another Defined Names button comes in handy. In that Ribbon are (Formula tab), click the down arrow next to "Use in Formula" to open the drop-down list. Selecting from that list fills in the Function Arguments dialog box.
I had to type "North" into the Criteria box, Excel adds the double quotes.
4. Click OK
In the formula bar, "=SUMIF(Regions, "North", Revenue_Million) appears. It can be typed exactly that way into the formula bar, without the dialog box or the drop-down list.
Incidentally, the same cell range can be both the Range and the Sum_range. For example, to sum just the cells for which Revenue_Million is less than 25, that's: =SUMIF(Revenue_Million, "<25", Revenue_Million)
The second argument (Criteria) is always in double quotes.
What about SUMIFs? That one is useful if you want to find the sum of revenues for North but only for the years 2006 and 2007. To find this follow these steps:
1. Select a cell for the formula result
2. Select Formulas | Math & Trig menu, SUMIFS
3. Enter data as pictured below:
No comments:
Post a Comment