Frequencies and Bins

Frequencies indicate the number of times a particular value appears in a set of values. To calculate a frequency in Excel, you need to use an array formula. The Frequency formula looks like this:

FREQUENCY(data_array,bins_array)

The "data array" represents the range of values that you are trying to calculate as a frequency. The "bins array" represents the intervals in which your frequency values will be placed.

In the below example, a student has been asked to come up with a frequency showing Average Earnings by male and female for the province of New Brunswick to determine the average earnings frequency. The cell references for the Male data cells are entered as the data array. The Bins Array uses a column showing the interval ranges as entered by the student. In this case, the intervals are set at $100, so the Bins represent ranges $100-199, $200-299, etc.

With the data array and bins array entered, the Frequency is then calculated.

More information on Frequencies can be found at:

FREQUENCY (from Microsoft Office Online)

How to Prepare an EXCEL Frequency Chart (from University of Edinburgh)

The EXCEL Frequency Function (from Meadinkent.co.uk)

---

TrendLines (or "Line of Best Fit")

To graphically display trends in the data, you can add trendlines to your Excel graph. This is especially useful for examing regressions and analyzing predictions.

To add a trendline to your data, first select your data in the chart and right click.

Choose from among the different options.

 

More information on trend lines can be found at:

MS Excel Tutorial: Lines of Best Fit (from GSCE Coursework site in the UK).

Using Error Bars and Trendlines (from Microsoft Online)

"Predict Trends in Charted Data using Trendlines" (from Inside Microsoft Excel)

---

Error Bars

Error bars are used to graphically represent potential amounts of error amounts relativefor a particular data series.

 

More information on Error Bars can be found at:

Using Error Bars and Trendlines (from Microsoft Online)

"Communicate Data Variability in your Charts with Excel Error Bars" (from Inside Microsoft Excel) (UTORid login required)

Error Bars in EXCEL Charts (from Peltier Technical Services)

---

Regression

Regression analysis examines the relationship of one or more dependent varaibles to a specified independent variable.

To perform Regressions in Excel, you will need to access the Analysis ToolPak. This is available under Tools...Add-Ins:

Select the Analysis ToolPak:

The Data Analysis tool will now be available under the Tools menu.

Select "Regression" from the menu

More information on performing regressions in Excel can be found at:

"Perform a Regression Analysis" (from Microsoft Online)

"Applied Regression Analysis" (pdf file from University of Queensland)

"Explore Data Relationships with Regression Analysis" (from Inside Microsoft Excel) ( UTORid login required)