Excel Thing

barneysplash

Hello all,

I have a query for any Excel heads out there.

I have a list of peoples ages for a healthcare project like this:

1
1
1
1
1
1
1
1
1
2
2
2
3
3
3
4
4
4
5
5
6
7
7
8
9
10
10
10

Does anyone know a formula that will say:

There are 7 1 year olds
There are 5 2 year olds
There are 6 3 year olds
etc.

And maybe put them on a bar graph.

Any ideas?

Thanks

Barney

mathew

There is a function called =frequency afaik. I think the syntax is =frequency(data, number)
Where data is the array of data to check through, and num is the age to check.

You would have to do each age in a different cell and then make a barchart of that group of cells.

d2x2

Use PivotTable reports and PivotCharts. No calculations or functions required! If you have never used them, you may find them odd at first but they are well worth learning. Anyway I give you all the steps below.

Name the column with all the ages 'Age' and select it.

-Start the PivotTable wizard from the Data Menu.
-All the defaults will be fine, until Step 3, where you will click Layout.
-Then drag Age onto the Row area; then drag Age onto the Data area.
-Double-click that last and change the Summarize by value to Count (instead of sum because you want to count the number of occurences of each age and not add them up).

Hey presto. Click OK as many times as needed to get out of the Wizard and you have the table created for you.

PivotCharts work in a similar way although you can simply click the chart icon once your PivotTable is created and it's as simple as creating a regular chart.

barneysplash

d2x2 wrote: »

Use PivotTable reports and PivotCharts. .

Ooooohh that's clever!

Worked perfectly, thanks for the help.

Barney