Responses-classification

**Dr.Appalayya** · 09-04-2009, 08:33 AM

I am administering a questionnaires on about 200 respondents; the questionnaire consists of about 40 questions. The questions elicit each respondent's views on a 7- to- 1 scale, besides respondent's demographic data. How can I classify them based on the respondent's responses after after entering them in an excel worksheet?

**Palmetto** · 09-04-2009, 09:51 PM

It will be easier to offer suggestions if you could either upload a sample workbook showing the data categories or provide more information on the type of data and what expectations/conclusions you are working toward. It is likely you will want/need multiple classifications to understand the data from different perspectives.

**Dr.Appalayya** · 09-05-2009, 04:58 AM

I have attached a sample data file. Please have a look and advise how I can classify the data.

**JBeaucaire** · 09-05-2009, 05:00 AM

Explain "classify the data". If your sample data sheet included another sheet with your desired "results" then we would be able to help you formulate that much easier.

**Dr.Appalayya** · 09-07-2009, 12:53 AM

I have attached a blank table of what have to classified. Please help.

**JBeaucaire** · 09-07-2009, 01:50 AM

The top section is all COUNTIF() formulas.

B2:
=COUNTIF([Datafile.xls]Sheet1!$A:$A,LEFT(A2,3)&"*")
(copy down 1 row)
(this formula is weird because your text strings don't actually match fully between the two sheets, so I'm just using the first 3 letters)

B6:
=COUNTIF([Datafile.xls]Sheet1!$D:$D,LEFT(A6,3)&"*")
(copy down 3 rows)

A12 = 2 (custom number format #" yrs")
A13 = 3 (custom number format #" yrs")
A14 = 4 (custom number format #" yrs")
A15 = 5 (custom number format #"+ yrs")

B12:
=COUNTIF([Datafile.xls]Sheet1!$E:$E,A12)
(copy down 2 rows)
B15:
=COUNTIF([Datafile.xls]Sheet1!$E:$E,">="&A15)

C18:
=SUMPRODUCT(--([Datafile.xls]Sheet1!$A$3:$A$13=C$17),--([Datafile.xls]Sheet1!$D$3:$D$13=$B18))
(copy down and over to fill chart.

F21:
=SUM(C18:D21)

The sample file is obviously connected to the Datafile.xls sheet, so you'll have to locate it and point to it when you open the sheet.

**Dr.Appalayya** · 09-07-2009, 11:41 AM

Thanks for your post, JBeaucaire. After practicing on it, I will get back to you for help. Thanks a billion.

**Dr.Appalayya** · 09-13-2009, 07:09 AM

Can we do the same by use of 'filter'?

**JBeaucaire** · 09-13-2009, 10:37 AM

No idea what you mean.

**Dr.Appalayya** · 09-16-2009, 01:31 AM

I do not understand how to use countif if I have to find the items that fall between certain criteria like: 3 to 5 years.

**Dr.Appalayya** · 11-06-2009, 01:59 AM

C18:
=SUMPRODUCT(--([Datafile.xls]Sheet1!$A$3:$A$13=C$17),--([Datafile.xls]Sheet1!$D$3:$D$13=$B18))

In the above formula, I don't understand the sign, "--"? How to typewrite it and what does it mean?