how to I determine frequency

S

Sandi

I have been asked to show frequency using the responses to the question "what
is your age?" The ages range as follows: Age
Question 1
27
34
21
41
53
61
24
26
37
30
40
65
20
21
45
37
58
38
40
69

Can someone please help me in quiding me in setting this up on a graph with
Excel and how would I briefly explain my findings in a couple of sentences?
 
M

Mike Middleton

Sandi -

You could use the Histogram tool of Excel's Analysis ToolPak, or you could
use my free Better Histogram add-in available from the Histogram page at
www.treeplan.com, perhaps with start, step, stop equal to 15,5,75.

Or, you could use worksheet functions like COUNTIF and FREQUENCY to obtain
frequencies, and then create a Column chart.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel
 
G

Gord Dibben

Sandi

To calculate Frequency you need two sets of numbers.

1. a set of numbers to count frequencies from.....the data_array

2. a set of numbers containing the intervals of frequency...the bins_array

Your sample numbers could be the data_array.

You would need a second set like

20, 30, 40, 50, 60, 70

Now you will get the frequency for 0 to 20, 21 to 30, 31 to 40, 41 to 50, 51 to
60 and 61 to 70

Using my sample as the the bins_array and yours as the data_array

Enter your numbers in A1:A20

Enter mine in B1:B6

Select C1:C6 and in C1 type in =FREQUENCY(A1:A20,B1:B6) then array enter by
hitting CTRL + SHIFT + ENTER keys all at once.

I get in C1:C6

1, 6, 6, 2, 2, 3

An alternative to this is a Histogram......probably easier........ from the
Analysis ToolPak add-in.

Load it and go to Tools>Data Analysis and select Histogram

Input range would be A1:A20

Bins Range would be B1:B6

Output range would be C1:C6

If you want the output sorted, check Pareto

For a graph, checkmark Chart Output


Gord Dibben MS Excel MVP
 
S

ShaneDevenshire

Hi Sandi,

You could also do this with a pivot table provided your groupings are of
equal size, 1-10, 11-20 , not 1-10, 11-15, ....

If you want this approach and are not familiar with pivot tables post back.
 
R

ryguy7272

Put your data in A1:A20, and the following in D1:D7;
10
20
30
40
50
60
70

This is the entire range of values.

Select 7 cells and type this:
=FREQUENCY(A1:A20,D1:D7)
Commit it with Ctrl+Shift+Enter...not just enter...

Regards,
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top