Excel simple question for a simpleton

[Tricky1234]

Hi

Please could someone help me. I can't get my head around this simple problem.

I have one worksheet called What is your age?, set out like this. Ages, then a column showing the total for each age group: Then a column for each person, with a 1 entered to indicate that persons age.
? 0-11 1
? 12-14 1
? 15-17 2
? 18-39 12 1 1
? 40-59 15 1
? 60+ 29 1

I have another worksheet which shows internet skills for the people above, still using the same columns:

10. In terms of your Internet skills, do you consider yourself to be?
A beginner 19 1
An intermediate user 24 1 1
An expert user 2
Have no Internet skills 15 l
I want to show patterns relating to age and their internet skills. Can someone suggest how I would go about for example creating a graph which shows the internet skils of those over 60+?

Thanks for any help

Richard

 

[RogADog]

It's best to start with a basic list, with one row for each person. Show the age group in Col B and the skill level in Col C.

Then you can make a pivot table to analyse your list showing the count. If you've not used pivot tables, you'll find the wizard quite straightforward to use.

The pivot table provides the data for your graph.

Hope it helps.

Rog

 

[klmorgan]

You may need to select the multiple consolidation radio button on the first page of the Pivot table wizard and set the field type to average so that you get the average score for that range.

If you set the table that you were collecting the data in to have three columns:

Name Age_Range Score

You could use validation to allow the choice of the age range


Regards

Keith

 

[SkipVought]

Hi,

You make a typical mistake that many spreadsheet users make when designing spreadsheets -- designing in terms of DISPLAY or REPORT FORMAT rather than DATA STRUCTURE. Your data structure is not NORMALIZED, which causes all sorts of problems when it comes to analyzing and reporting from your data. Check out 'Fundamentals Of Relational Database Design'

To be able to work with yor data, it must be restructured, maybe something like this...
[tt]
Person Table with Named Ranges
Person DateOfBirth

Skills Table
Person SkillName SkillLevel AgeGroup

[/tt]
I suggest SkillName as a column in this table since you might want to add skills for Word Processing or Spreadsheets etc.

I also built a table for Age Groups
[tt]
Age Agroup
0 0 - 11
12 12 - 14
15 15 - 17
18 18 - 39
40 40 - 59
60 60+
[/tt]

The formula for AgeGroup is
[tt]
=INDEX(Agroup,MATCH(INT((TODAY()-INDEX(DOB,MATCH(A2,Person,0),1))/365.25),Age,1),1)
[/tt]

THen perform a PivotChart on the Skills Table using Count of SkillLevel and you have your chart.






Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244