how to I determine frequency

[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?

 

[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

 

[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

 

[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.

 

[Herbert Seidenberg]

I second Shane's Pivot Table approach.
Advantages:
Refreshable
No formulas
Automatic graph
Easily expanded
Clickable options
Here is a sample file:
http://www.freefilehosting.net/download/3abe1

 

[ryguy7272]

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

This is the entire range of values.

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

Regards,
Ryan---