Instructor: Next up, let's talk
about the frequency function which technically isn't
a new dynamic array function
but it's a great example of an old
or legacy formula that has become much more user friendly
and much more powerful
with this new dynamic array functionality.
So the frequency function is designed to return
the frequency of values
in a range based on specified intervals or bins.
Now if you hear us using terms like frequency or bins,
odds are we're talking about the distribution
of numerical values in a data set.
And frequency is a great function
that we can use to analyze distributions
along with visuals like histograms and preto charts.
So syntax wise, only two parameters here.
The first is the data array
which is the array of cells that contain numerical values.
And the second is the bins array.
This is an array of intervals or bins
which will be used to define how you group
or bin those values.
Now, important note here, that bins array argument
it needs to be structured in a very specific way.
It's gonna be formatted as the upper limit
of each bin formatted as a number.
And I'm gonna show you a quick example of that now.
And then again, once we jump into Excel.
So here's a simple visual example here.
We've got our product category
and sales data in columns A through C.
And what we're doing here is using a frequency
function pointing to those numerical sales values
in C2 through C10
and telling Excel we want to return the frequency
of those sales values or records
in our original dataset based
on the intervals specified in sales F2 through F6
and the resulting spill range is gonna look
something like this.
Here we're counting the frequency of sales records
which fall into each bin represented
in sales F2 through F6.
So there are four sales values or records that fall
under 3000, and we can see those are rows 3, 4, 5, and 9.
There's one record between 3001 and 6,000.
There's two records between 6,001
and 9,000 and so on and so forth.
Last thing to note here, remember the frequency
function will always return one extra row.
That's kind of the overflow row to account for any values
above the largest defined bin, which in this case is 15,000.
That's why we see a one in that final row
because in row two we've got a sales value of 19,000
which exceeded the largest interval.
So that's the frequency function in a nutshell.
Let's jump into Excel
and practice applying this to some real data.
Alright, so go ahead and open
up your dynamic array formulas workbook.
We are now in the frequency tab.
We've got the same familiar raw data
that we've been working with.
Industries, job titles, cities, salaries
and growth rate.
And for this demo
we want to better understand the distribution
of these salary values in column D.
So what I've done is set up some prebuilt intervals
or bins which start at 25,000 and go all the way
to a maximum of 175,000 with intervals of 25,000.
So these descriptions here in column G are really
just to help understand which values are gonna fall
into each of these interval buckets or bins.
So that's the setup note that I'm using.
The upper limit of each bin
it's formatted as a number here in column H.
And now all I need to do is type A frequency function,
right here in I3 and reference those intervals or bins.
So equals frequency, my data array
the array of values that I'm analyzing.
Well, those are my salaries in D3 through D842.
I can scroll back up.
The only other parameter or argument
of this function is the bins array
which I've already created right here
in H3 through H9.
Close it off, press enter.
And there you go.
I've got my distribution, my frequency
of salaries that falls into each of these predefined bins.
And again, I get one extra row
in case there are any salaries
in my data set that exceed my maximum value
which in this case would be any salary over 175,000.
And this process is very similar
to what we would do if we were to simply
insert a histogram to visualize the distribution.
So if we were doing that
I'd select the same data array, right?
I'd go to insert and I'd select a histogram here.
And let's move this up.
I'm gonna cut that chart,
scroll all the way back to the top, paste it right up here.
There we go.
You can see Excel has defined whatever it deemed to be
appropriate intervals or bin ranges here.
What I'm gonna do is format that axis
and I'm gonna basically replicate the same bins
that I just created by hand.
So my bin width is 25,000.
My overflow, which is basically my max is 175,000.
My underflow, which is the minimum is 25,000.
And check this out.
I get the same intervals that I created by hand.
I see that there's a count
of three salaries in the first bin.
214 in the second, 284 in the third, and so on.
And that matches exactly
to the values produced by my frequency function.
So up to you, if you wanna make this look pretty, go for it.
We can call this something like distribution.
There we go.
And we could right click format
that data series just to make it consistent.
Give it a nice navy blue.
And there you go.
We've got our histogram that's visually
telling the exact same story.
Now, final step here just to take it to the next level.
Let's say that instead
of defining a fixed set of bins or intervals, you want
to make that a little bit more flexible and dynamic.
So you set up something like this, right?
You say, okay, put in whatever you want for the max value
like 150,000 instead of 175
and then determine what you want
as your interval or bin width in this case, 30,000.
And then based on those inputs, let's try to basically
generate the bins and the frequency function based
on whatever values you've selected.
So to produce my bin array using these two values
what I'm gonna do here is use a sequence function.
So I'm making use of these dynamic arrays and the number
of rows in my spill range needs to be based
on my max value and my step value.
So in this case, if we were to use a max
of 150,000 and a step of 30,000
that should produce five rows or five distinct bins.
The first would be 30,000,
the next would be 60, then 90, then one 20, then one 50.
And to produce that value can simply divide the maximum
by the step, and that's gonna dictate the rows
that your sequence function should return.
So let's go ahead and input that into our rows argument,
L3 divided by L4.
We don't really want any additional columns
so I'm gonna comma past that parameter.
Our start value is the step or the minimum.
And the step is that same value L4.
Close it off, press enter.
And there we go.
We have our upper limits of these five bins.
This were 120,000.
You'd see only four values for breaking it down
with steps of 40,000.
Now we only have three.
Let's go ahead and get back to where we were.
That looks like a pretty good set of intervals.
And now you know the drill, same frequency function
that we used before data array hasn't changed.
That's D3 through D842.
And our bin array is this spilled range that we just created
with the sequence function.
So let's use K7 pound, close it off press enter
and there you have it.
And as a little extra bonus, another nice way to,
kind of give this a visual flare, kind of like
the histogram style would be to reference that spilled range
of values and apply something like, you know
data bars here.
Conditional format to basically accomplish
a similar thing is a histogram here.
So just like we did earlier in the course
can manage that rule, can edit it,
use a consistent navy blue color, and again,
click that bar only
so that we're not duplicating the values.
Press okay, apply.
And there you go.
So two different ways that we can visualize
and analyze distribution of numerical values
in a dataset using the frequency function.