## The Frequency and COUNTIFS Function: histograms

Introduction

The Frequency and COUNTIFS() functions are both very convenient ways of preparing frequency distributions that we can use to draw a histogram. The purpose of this page is to demonstrate both the frequency functions and the COUNTIFS() function in this context. The page will also demonstrate that the frequency function can be used either as an array function or as an ordinary non array function. The way they work is different and the answers they give are initially different although with a little bit of effort we can make them the same!

Frequency ARRAY Function

In the Excel workbook that accompanies this page (link at the bottom of the page) you will find a large amount of data: the temperature recorded somewhere on earth every month from 1907 to 1972. I wanted to prepare a histogram to see the distribution of temperatures over the ranges, or boundaries, of:

0 – 10 degrees

10 – 20 degrees

and so on

With the temperature data in a range named temp, this screenshot shows what that looks like: To program the frequency column with the array version of the function,

• select the range H9:H17 and type =FREQUENCY(temp,G9:G17)
• now press Ctrl+Shift+Enter or, on a Mac, press Cmd+Enter

The range H9:H17 should now fill with the appropriate values for each temperature range.

You can now draw your histogram to look like this That’s it!

Frequency Non ARRAY Function

In the non array version of the frequency function there is an additional step to take to achieve the same result as we have just seen for the ARRAY version of the FREQUENCY function. We set up the function differently too:

• in cell H9 enter =FREQUENCY(temp,G9) and press Enter
• now drag that down to fill the range H10:H17

Notice the difference between the two versions of the function. That is, the non array frequency function gives us the CUMULATIVE frequencies and to show the frequencies for each class interval we need to create the following … for demonstration only I have created an extra column but this column is all your need:

• in cell J9 =FREQUENCY(temp,G9)
• in cell J10 =FREQUENCY(temp,G10)-SUM(J\$9:J9)
• in cells J11:J17, copy down (fill) the formula in cell J10

You will now see this:  COUNTIFS

We can also use the COUNTIFS() function to find the frequency distribution of the temperatures too, as follows:

• in cell K9 =COUNTIFS(temp,”>=0″,temp,”<10″)
• in cell K10 =COUNTIFS(temp,”>=10″,temp,”<20″)
• in cells K11:K17, copy down (fill) the formula in cell K10 and make sure you change the values of >= and < for each row

You will now see this: Not the Same

Ah! Did you notice that the results are not all the same? Did you see that the results of the frequency formulas are a little different from the results of the COUNTIFS() formula? Look again at the final table above.

What has gone wrong? Nothing has gone wrong; except that I programmed the COUNTIFS() function a little tighter than then frequency function. I did this by saying in cell K9, for example, =COUNTIFS(temp,”>=0″ …) and in cell K10, as another example, =COUNTIFS(temp,”>=10″ …). Note the >= aspect of those formulas. In the frequency function formulas, though, I had the class boundaries set at 10, 20, 30 and didn’t say >= at any stage.

Because the temperature data are all recorded at just one decimal place, I felt safe in restating the class boundaries as:

9.99999

19.99999

29.99999

But since that doesn’t look so good, I formatted cells G9:G17 to show no decimal places so it rounded the numbers to the nearest whole number: this is just for cosmetic effect.

I then changed the formulas in the class intervals column by rounding to zero decimal places for cosmetic effect again:

• in cell F9 =0&” – “&ROUND(G9,0)
• in cell F10 =ROUND(G9,0)&” – “&ROUND(G10,0)

and so on

Everything looks good now and everything is consistent too, so we can compare the two versions of frequency and COUNTIFS quite easily.

Conclusions

There you are, a little drawn out in some ways; but this demonstrates that Excel commonly provides more than one way to skin a cat!

I like to use array functions when I can because they contain the feature that not many people know about them so they find it very difficult to mess with them!

I like to demonstrate the non array frequency function for those of us who find array functions a little daunting.

I like to use the COUNTIFS() function because it is relatively new and it is very versatile and well worth learning to use in as many situations as possible.

### 2 Responses to “The Frequency and COUNTIFS Function: histograms”

1. Per-Eric Says:

Very helpful and nicely explained !
But I can not get the copy down (fill) to work!
any suggestions? using Excel 10

Regards
Per-Eric

in cell K9 =COUNTIFS(temp,”>=0″,temp,”=10″,temp,”<20″)
in cells K11:K17, copy down (fill) the formula in cell K10.

• excel2007master Says:

Sorry for the delay in replying Per-Eric

Did you download my Excel file for this? In case you did not, here are the formulas for K9 to K17, starting with K(

K9 =COUNTIFS(temp,”>=0″,temp,”=10″,temp,”=20″,temp,”=30″,temp,”=40″,temp,”=50″,temp,”=60″,temp,”=70″,temp,”=80″,temp,”<90")