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.

you may not be a scientist but you just saved an engineering student a huge headache. Great video, thanks a bunch

Thank you very much.

Thanks – big help in an assignment I am desperately trying to complete!

This was the most helpful!!! I love it being so simple:D

Thank you soooooo much

Hey. thank you. it helped me very much.. I love the way its short and simple, unlike all the other exceltutorials.

thank you! great presentation

this didn't work

Thank you so much! This is the 3rd video I watched before I finally understood it!!! =)

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

thanks m8 was really helpfull

serious man very thankfull

THIS SAVED MY ASS FOR MY STATS FINAL PROJECT!!! THANK YOU FOR SHARING

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

hmm, same here 🙁

very googd :))

Thank you so much!

thanks

thanks heaps

I have done pivot as you explained but the Group Field Option is greyed out. Cant select it. What to do?

had a homework on statistics. you're a big help, thank you 🙂

YOU JUST SAVED TWO PEOPLES LIVES. I will sleep tonight. Thank you so much. You are an angel from heaven.

Oh my god you just saved my grade thank you!

Thanks, that was a HUGE help!!!

thank you! you saved the day- awesome!!

This was so helpful!!!! Awesome Job Bill !!!! and a big thanks!! it saved my time

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

i still cannot get it!

nerd ;o

thanks

thanks

SUPER!! YOU HAVE SAVED MY LIFE 😉

That is what I was looking for!!!

Bill you are the best!!

"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

Thanks a lot… this was really helpful… 🙂

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!

THANK YOU!

thanks bill

Excellent video thanks for the help

TAnks Billllll 😀

if i have a mac am i screwed.

Thank you!

So many things I did wrong and my formula wouldn't work.

Thank you so much!!

I'd been stuck in this for big awhile, Thank you vey much teacher 😀

Bro, Bill. Need a JDBC connection to your brain before my excel exam 😛

You saved me from spontaneous combustion! Thank you!

Thank you. This video was a lifesaver!

I've been trying to figure out how to do this for two days now.

Thank you so much.

Very, very helpful! Thanks a lot!

i have no fucking clue… fuck this I cant do it.

Thank You so mu

ch!!! I was about to start pulling my own hair out

Thank you so very much Bill.

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

I LOVE YOU

THIS MAN WAS PUT ON THIS EARTH TO MAKE THIS VIDEO FOR ME….GOD BLESS YOU BECAUSE YOU DEFINITELY JUST SAVE MY LIFE.

thank u sir

Really useful！ Thanks！

Thanks for the help. Excellent, easy tutorial!

this saved my life

Once again the exact answer I was looking for in a concise video.You are the man!

Thanks!

This is an excellent tutorial <3 Simply perfect

for time interval how can we do histogram? Please help me on this

Thank you so much, extremely helpful during college final's week.

Found it. Thx.

oh my god i love you mister

This tutorial really help thanks much

THANKS VERY HELPFULL

Thanks!

I appreciate it, you really helped. I was stuck on this for the longest time.

I love you! Thanks for this video!

Thanks………..

Thank you so much! It works!

thank you so much, omg

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

Thanks

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 🙂

Great video! Pivot table example especially helpful.

Thank you very much

Am I a nerd If I watch these videos for fun, and that I think BIll Jelen is a God?

no picture just voices

Thanks Bill. This was short, sweet, and to the point. Just what i needed.

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

You are amazing. For the "Group field", I have to subscribe your channel.

Thank you for a quick lesson on the frequency function and the alternative of using a pivot table

Very helpful. Thank you!

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.

Thank you Bil. Very helpful. Got it!

thanks a lot, God bless you. you save my time

must the bin array be in ascending order?

Thanks for this! 🙂

Thank you thank you thank you SO MUCH.

Thanks, video is much more helpful than reading help guide

why does mine not work??? 🙁

I still couldn't do it. ;-;

thank you very much. a good demonstration and easy to apply.

Thanks it's really useful !

very nice. I'll subscribe you.

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

I love you, you are the only one who clarifies you need to extra square selected for the Bins array!

If you like this video, please consider subscribing and clicking the bell icon. Thanks!

i never thought i'd see someone so excited about excel, thank you bill i love you