Bill: Hey, I’m Bill Jelen from Mr. Excel.com, I’ve got a cool Excel tip for you today.
Mike: Hey, this is Mike Gel Girvin at Excel is Fun in Youtube and I have a different way to do that.
Bill: Hey, alright, welcome back, it’s another Dueling Excel podcast. I’m Bill Jelen from Mr. Excel, mixing it out with Mike Girvin from Excel is Fun, question of the day is how to create a frequency distribution from this data now. I’m gonna let Mike go first today, he’s gonna show us some formula ways, we’ll come back and talk about pivot table, way to do the same thing.
Mike: Thanks Mr. Excel, hey, we have our data set here, we want to build a frequency distribution. Now, we have some values, we want to group them and then count to get the frequency. So our first group will be ten up to fifteen, so we’re got a count of one. Our second group will be fifteen up to twenty, we’ll get a count of one, two, three, that frequency will be three. I’m gonna use the frequency function, but the frequency function, you have to tell it what the upper limit is for each category. So, I’m gonna type upper, type a 15 and a 20, highlight those and then our little filter handle, click and drag. You can see, because we’ve established a pattern, add 5, it knows to increment those. Now, I’m gonna type frequency, and our frequency function requires that we highlight the cells, we have one, two, three, four, five categories, so five cells, and we need to have five upper limits, then we’re gonna type frequency. It wants the data, comma, and the upper limits, oops, the upper limits here. Now, what is the frequency function to do when it gets to this category here, it’ll say 15 up to an including 20. So the upper limit is included when you use the frequency function. Control-shift and enter, this is an array function, control-shift-enter. Alright, now, what if we didn’t want this 15, we wanted the 15 down here, so, 15 up to 19.99, well, I’m gonna just type a penny down here, 0.01, copy, highlight the range, right click, paste special operation. So right here, we’ll say subtract and instantly it will subtract a penny. We have some new upper limits, the frequency function Cs, the new upper limits and boom, it counts exactly what we want. Now, this is a report, maybe not as explicit as we want. Maybe we want our labels to, say ten up to 19.99, I’m gonna click on this sheet, and if we scroll over here, we have categories, ten up to 14.99, 15 up to 19.99, so I created this categories so there’s no confusion about the definition of each category and which count goes into which category. Now, frequency function, upper limit, it includes upper limit, sometimes you do not want that. Let’s look at a different example here, if you have ten up to 15, a label like this and the 15 is not included, so ten up to 15. Well, the frequency function won't work if you give it a 15, it will work with the 14.99, you’d have to use formula like this. This some product, for example, you check that whole range for less than the upper limit, check the whole range a second time, greater than or equal to the lower limits, so you see the lower limit is included with this formula, the upper limit is not, and it counts everything in between. Now, that’s fine, another option, if you have 2007 is used to count if’s, with an S, so there we have our criteria range and our criteria, notice we had put the comparative operator in quotes and ampersand the upper limit, same with the second criteria, greater than or equal to the lower limit. If that wasn’t enough, you could also do two count if formulas. Now, these examples we just saw some product counters, that’s for when the lower limit is included but the upper limit is not. But if you got your upper limit and it is included, wow, frequency function, nice straight forward. Alright, I’m gonna throw it back to Mr. Excel.
Bill: Mike, those formulas are enough to make my head spin, let’s take a look at the easy way to do this. I’m gonna come in here to my data and we’ll use insert, pivot table, click OK, now I’m gonna, I wanna put sales down the left hand side and then sales again in the sum values. Now, that’s gonna put it in as sun of sales, but I wanna change that, I’ll choose the first one, go to field settings, instead I wanna show this as a count. There we go, so basically we see every item appears exactly once it’s almost useless, but, check out what we can do. I’m gonna go to the very first item, right click, choose group, and then say I wanna start at 10, go up to 35 in 5 unit increments, I’ll go OK, and bam, there we are. We’re done, our frequency distribution, great, great way to create this without creating any formulas. Hey, in behalf of Mike and myself, I wanna thank you for stopping by, I’ll see you next time for another Dueling Excel podcast.
Transcription by:
Scribe4you Transcription Services