Excel In Depth – Frequency Distribution: Podcast #1258

Excel In Depth – Frequency Distribution: Podcast #1258

MrExcel podcast is brought to you by Easy-XL. Excel In Depth Chapter 14: Frequency Distribution Oh, hey, welcome back to the MrExcel netcast. I’m Bill Jelen. In chapter 14, we’re talking about a lot
of statistical functions. In particular, for the podcast, I chose the
FREQUENCY function just because it is so hard to use. What FREQUENCY is used for, when you want
to group things into what scientists call bins. I’m going to use the more friendly term
of buckets. So what I have here is I have about 4,000
results. The results range from about 10 on up to 90. They’re in one decimal place. So 49.5 kinds of things. And I want to find out how many of those results
were less than 10, how many were from 10 to 19.9 and so on. And so to make this work, you have to build
something called a ‘Bins Array’. And what I’m going to do is, I’m going
to build a Bins Array and say, “Hey, I want to see all the records that were 9.99 or less
and then everything from, essentially, 10 up to 19.99.” I’ll need to drag that down here and we’ll
go down to 89.9. Alright, so that’s my Bins Array. And notice that the Bins Array is always going
to have one less item than what you want to return. Right, now to create the frequency function,
here’s what you have to do. You have to select several cells at once,
okay. So the cells adjacent to your Bins Array plus
one extra cell. We’re going to enter one formula in this
entire range. So ‘=FREQUENCY’. I’m going to say, “Okay, where’s your
data?” My data’s over here from A2 down to A4481,
comma, and then where’s the Bins Array? The Bins Array is to the left of my data. Let’s include that. And you notice the Bins Array includes one
less cell than the number of cells we have selected. Alright, now, because this is a formula that
returns many results and those results are going to be placed in several cells, we have
to hold down Control and Shift and press Enter. And we now have the results here based on
this Bins Array. So less than 9.99, zero records. But from 10 to 19.99, basically 10 to 20,
you have 65 there. Okay, so it’s a nice formula. It keeps updating. Now, if you just need to create this as a
snapshot, I’m going to suggest you don’t use the Frequency Array formula at all. You can just use a pivot table. Insert>Pivot Table. Click OK. Now on our Pivot Table Field List, we’re
going to take the result field and drag it down to Row Labels and then take the result
field and drag it to Sum Values. Now, over there in Sum Values, we’re going
to change the value field settings to show a count. Alright. And so, yeah. Alright, great. It’s saying that there are one record for
10, one record for 10.2, 3 records for 10.3 and so on. But what we want to do is we want to choose
one of those value fields. Come up here to Group Field and say that we
want to group this from 10 to 89.9 in 10 unit groups. Click OK. And there’s our pivot table now showing
that there were 65 records from 10 to 20, 171 from 20 to 30, and so on. Matching the results we got there on the FREQUENCY. The FREQUENCY function still has a use, definitely,
because it can automatically update as the numbers change or as we change the Bins, but
if you’re just trying to get one… Maybe just a quick snapshot of the frequency,
you can use the pivot table instead. Right, I want to thank you for stopping by. We’ll see you next time for another netcast
from MrExcel.

100 thoughts on “Excel In Depth – Frequency Distribution: Podcast #1258

  1. OMG you just saved my cousins butt your tutorial was simplistic and help her understand Bins array etc to get the frequency data to be correct

  2. That's pretty sweet. My instructor showed us the =Frequency() formula, but it's cool to see some other uses for pivot tables.

  3. Thank you so much! that was very helpful; I've was struggling for about 40 minutes, lol! I had no idea you were suppose to use ctrl+shift+enter

  4. "what do you mean by "as a snapshot" …is one way of finding frequencies worse than the other you showed? What is the mathematical difference in the 2 ways you showed of finding frequency? Thanks

  5. I chose to use the pivit table. Im supposed to generate a sample of 180 numbers between 1 and 60. When doing the pivot table the "GRAND TOTAL" is 179. Should it be 180 instead? what am I doing wrong?? PLEASE!

  6. Wow!  that was seriously cool!  Is there a way to group in Excel pivot tables using a formula to determine the group?


  8. Hey sir please would you mind giving me an email or website to contact for business goals.

  9. Thank you sir! I vaguely remembered I had to press some combination of keys to make this work and your video quickly helped me do just that 🙂

  10. As an exhausted college student doing his last minute project. I thank you so much for your wizardry sir. I owe you a beer.

  11. Thank you, you are the most helpful to me when searching for instructions. I tried 3 different ones, and only your simple technique worked for me and my computer.

  12. Oh shit i find this vidio after my exam😣 😖😭😭😭 and the only one question is that and i dont know about this 😣😳😳😓😭😭

    But tnx for the vidio

Leave a Reply

Your email address will not be published. Required fields are marked *