Histogram Excel 2008 Download All Examples

To achieve this, use the COUNTIFS formula: COUNTIFS ( D: D, H3 ).

It groups your data into bins or classes and shows the number of items per bin.

For example: Your data has Big Mac prices in different countries.

A histogram shows how many countries have a Big Mac price between 1 and 2 USD, 2 and 3 USD and so on.

Histogram Excel 2008 Download All Examples

Please also feel free to download all examples in an Excel file.

You got a table with two columns like shown on the right-hand side.

You could switch it to Bin width and set the desired class width, e.g.

USD.

Excel then summarizes all items below the underflow bin into one column on your chart.

You insert a PivotChart and use the function to group the data.

Also, the PivotChart Fields pane on the right-hand side opens.

Then drag and drop the item names (here: Country) into the Values section.

Make sure, that it says Count of Country instead of Sum of Country.

If it doesnt say Count of (e.g.

Sum of), right-click into your PivotTable in the column of Sum of Country, click on Summarize Values By and check Count.

Therefore, you have to prepare a table with the class limits and insert the class into your existing data.

For each class, it has the lower limit, the upper limit, the average, a class name as well as the number of items (here: countries).

The final chart will only refer to the last two columns of this table.

Recommendation: You could also make it dynamic, so that the user can define the class with (cell J1 in this example has the class with).

E.g. cell C3 has this formula.

You will then return the class average, which you insert into column H.

This class name will be shown in your chart and should be easier to read.

You can do this with this SUMIFS formula: SUMIFS ( H: H, F: F, C3, G: G, C3 ).

The SUMIFS formula returns the class average from column H under the following conditions: The value in column F (the lower range limit) is smaller than your search value (here the dollarprice in cell C3).

The second condition is that the upper range limit (column G) is larger than the search value (again cell C3).

Please refer to this article for more information about classifying data with the SUMIFS formula.

Count how many items of your original data in column A are within the range of each class.

Histogram Excel For Mac 2008