Excel Video 129 uses the frequency function to group data into ranges or buckets. Frequency is an array function. If you haven’t seen arrays, they work differently than traditional Excel functions. To tell Excel you’re entering an array formula, you need to hit Control+Shift+Enter when you enter the formula. Excel puts brackets {} around the formula to indicate that it is an array. You can use arrays for a variety of functions that we’ll discuss in future Excel Videos. Also note that when I entered the frequency array, I selected all of the cells I wanted the formula in, entered the formula once, and Excel copied the formula into the remaining cells and calculated the frequency appropriately. You’ll see that I can’t delete just one cell that’s part of an array. If I want to delete a cell that’s part of an array, I’ll need to delete all of the cells in the array.
The frequency function is pretty straight-forward. The first variable is the range of cells with the data you want to group, the second variable is the range of cells that describes the buckets you want the data grouped into. Notice that I have a bucket in my range above and below the range of my data (0 days and 210 days) to make sure I capture all of the data in a bucket. Frequency works by counting the number of cells less than each bucket, so the number next to the 30 bucket is the number of cells less than 30 but greater than the next smaller bucket.
I find it easier to add a column of formulas defining my range next to the frequency formulas. You’ll see how I concatenated formulas and text with the & sign to make those descriptions work. If you missed the earlier Excel video, concatenate means to add something to the end. I take one cell, add a space, a hyphen, and a space, add another cell, add a space, and the word “days” to get a formula I can copy down.
Arrays are tricky to start with, but as we do more examples, you’ll see arrays can be a very powerful Excel trick. Next time we’ll go through an easier example and chart the frequency we just calculated.
