advice request

[rgraham1]

Hi,

I'm looking for a little advice. I have volunteered to use Excel to
analyze the results of a big bunch of surveys that some teenagers from
the local school have created and answered. And I'm looking for your
advice on how to go about it, before I get started down the wrong
track.

The survey is about 35 questions long. It's mostly multiple choice
questions (~90%). There are between 2 and 10 multiple choice answers
to pick from on these questions. About 10% of the questions have some
type of open ended text responses (2 lines at most for these).

We have agreed to use Excel to compile and analyze the data so as many
people as possible can work with the data.

There are about 700 survey sheets to enter and analyze (each sheet has
the 35 questions).

The data entry will be done by about 10 different volunteers with
various versions of MS Excel (both Windows & Mac).

My task is to: 1) create an Excel template that I can email to the 10
data-input volunteers so they can keyboard the data from the survey
sheets into the template. 2) Receive by email the 10 different Excel
data files that the volunteers will create from my template. 3)
compile/merge the 10 Excel files into one master file. 4) Be able to
analyze and present the data from the 700 survey sheets upon the
community's request.

I was thinking that I would create a template that has the 35 questions
and possible multiple choice responses on the first sheet of the Excel
page and then try and duplicate that 1st page for each of the 700
surveys, but it doesn't seem like a very elegant way to handle this.
Can you think of a better way to do it?

Your help would be greatly appreciated. All of the participants have
volunteered their time and effort. No one will benefit financially
from this project.

thanks,
rgraham1

 

[RagDyeR]

One approach, for the multiple choice questions at least, might be to simply
have Column A reference the questions, and have each adjoining column
reference an individual questionnaire.

With 700 surveys split among 10 volunteers, that makes just 70 columns
necessary for your template.

With just 35 questions, you could start your tally formulas at the bottom.

Something simple, such as counting the number of the multi-choice answer (1,
2, 3, ... etc.).

=COUNTIF(B1:BS1,COLUMN(A:A))

Copy across 10 columns ( max number of choices),
And down 35 rows (max number of questions)
You'll have to exclude the text answers of course.

Then just total the 10 templates.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi,

I'm looking for a little advice. I have volunteered to use Excel to
analyze the results of a big bunch of surveys that some teenagers from
the local school have created and answered. And I'm looking for your
advice on how to go about it, before I get started down the wrong
track.

The survey is about 35 questions long. It's mostly multiple choice
questions (~90%). There are between 2 and 10 multiple choice answers
to pick from on these questions. About 10% of the questions have some
type of open ended text responses (2 lines at most for these).

We have agreed to use Excel to compile and analyze the data so as many
people as possible can work with the data.

There are about 700 survey sheets to enter and analyze (each sheet has
the 35 questions).

The data entry will be done by about 10 different volunteers with
various versions of MS Excel (both Windows & Mac).

My task is to: 1) create an Excel template that I can email to the 10
data-input volunteers so they can keyboard the data from the survey
sheets into the template. 2) Receive by email the 10 different Excel
data files that the volunteers will create from my template. 3)
compile/merge the 10 Excel files into one master file. 4) Be able to
analyze and present the data from the 700 survey sheets upon the
community's request.

I was thinking that I would create a template that has the 35 questions
and possible multiple choice responses on the first sheet of the Excel
page and then try and duplicate that 1st page for each of the 700
surveys, but it doesn't seem like a very elegant way to handle this.
Can you think of a better way to do it?

Your help would be greatly appreciated. All of the participants have
volunteered their time and effort. No one will benefit financially
from this project.

thanks,
rgraham1

 

[RagDyeR]

Forgot to lock the references for copying.

Formula should actually look something like this:

=COUNTIF($B1:$BS1,COLUMN(A:A))
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


One approach, for the multiple choice questions at least, might be to simply
have Column A reference the questions, and have each adjoining column
reference an individual questionnaire.

With 700 surveys split among 10 volunteers, that makes just 70 columns
necessary for your template.

With just 35 questions, you could start your tally formulas at the bottom.

Something simple, such as counting the number of the multi-choice answer (1,
2, 3, ... etc.).

=COUNTIF(B1:BS1,COLUMN(A:A))

Copy across 10 columns ( max number of choices),
And down 35 rows (max number of questions)
You'll have to exclude the text answers of course.

Then just total the 10 templates.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi,

I'm looking for a little advice. I have volunteered to use Excel to
analyze the results of a big bunch of surveys that some teenagers from
the local school have created and answered. And I'm looking for your
advice on how to go about it, before I get started down the wrong
track.

The survey is about 35 questions long. It's mostly multiple choice
questions (~90%). There are between 2 and 10 multiple choice answers
to pick from on these questions. About 10% of the questions have some
type of open ended text responses (2 lines at most for these).

We have agreed to use Excel to compile and analyze the data so as many
people as possible can work with the data.

There are about 700 survey sheets to enter and analyze (each sheet has
the 35 questions).

The data entry will be done by about 10 different volunteers with
various versions of MS Excel (both Windows & Mac).

My task is to: 1) create an Excel template that I can email to the 10
data-input volunteers so they can keyboard the data from the survey
sheets into the template. 2) Receive by email the 10 different Excel
data files that the volunteers will create from my template. 3)
compile/merge the 10 Excel files into one master file. 4) Be able to
analyze and present the data from the 700 survey sheets upon the
community's request.

I was thinking that I would create a template that has the 35 questions
and possible multiple choice responses on the first sheet of the Excel
page and then try and duplicate that 1st page for each of the 700
surveys, but it doesn't seem like a very elegant way to handle this.
Can you think of a better way to do it?

Your help would be greatly appreciated. All of the participants have
volunteered their time and effort. No one will benefit financially
from this project.

thanks,
rgraham1

 

[rgraham1]

RD,

So, across the top I'd have the titles.

And in column A: I would have each row reference a question for the
first questionaire (A2 => question #1, A3 => question #2, ect)?

And in Column B: I would have each row reference a question for the
second questionaire (B2 => question #1, B3 => question #2, ect)?

Is this correct?

Ron

 

[Ragdyer]

YES
If you wish, all of Row1 is title headers A1 to BS1
Column A is just the question number:
A2 = question1
A3 = question2
A4 = question3
.... etc.

If the survey is not anonymous, you could enter the respondents name and/or
number in B1, and then *just* the answers down Column B.
Next respondent (questionnaire) in Column C.
Next respondent (questionnaire) in Column D.
.... etc.
Out to Column BS (70 columns)

So, B2 to BS35 is filled with nothing but just numbers, the number of the
multiple choice selection picked by the particular respondent to answer that
question.

As the formula is copied *across* the columns, it automatically increments
itself to give you the total 1's, 2's, 3's, ... etc. that were used to
answer that particular question.

To perhaps make the totaling easier to understand,
In A40 enter "Quest #"
And in A41 enter "Q1"
And copy down to A75 to end up with Q35

Fill B40 to K40 with the numbers 1 to 10

In B41 enter this formula:

=COUNTIF($B2:$BS2,COLUMN(A:A))

Copy it across to K41
Select B41 to K41 and copy down to K75

You now have a complete tally of answers chosen for each question for each
multiple choice.
You'll see a lot of zeroes, since you said the possible choices ranged from
2 to 10, so the formula is looking for 10 possible numbers to count.

 

[rgraham1]

Ahhh, I should have provided more details initially on the survey...

The questions can not be answered with a simple number response. There
can be 1 or more answers per question. Many questions are a YES/NO
response. However, there are a significant number of questions that
can have up to 6 choices (eg. "check all that apply"). As well, we do
have a few the the free text replies.

I hope this doesn't throw a wrench into the engine.

Ron

 

[Ragdyer]

What you *NOW* describe cannot be accomplished with just formulas.

Post to the programming group, and see if someone will work up some code for
you.

Make sure that you *fully* describe *exactly* how the answers to the
questions will be configured.

 

[rgraham1]

RD,

Thanks for your suggestions.

I'm not willing to give up so fast though.

I have a couple ideas.

Can I use a different "Sheet" for each Survey (eg. Sheet1, Sheet2,
Sheet3, ect)? A template like you suggested might be used for each
Sheet. I would have the questions go down (question #1 goes in row #1,
question#2 goes in row#2, ect). The answers would go in each column
next to its question.

Or a second alternative would be to have each question on its own
Sheet. Then each row would be a new survey and each column would be
the the answers to that sheet's question.

If I go with the above can I create graphs that span across different
Sheets?

The last idea I had was to have the each Survey be in its own row and
then each Question would occupy a group of 6 adjacent columns (Question
#1 => B2, C2, D2, E2, F2, G2; Question #2 => B3, C3, D3, E3, F3, G3;
ect)

Might one of these ideas work?

Ron

 

[Ragdyer]

I didn't say give up!

Multiple answers to the same question.
Some "Yes" and "No".
Some with either a single response or 6 responses.
You need code!

The programming group has a bunch of folks who might jump on such a project
and gladly offer suggestions.

 

[rgraham1]

RD,

When I hear Programming I think of it costing money or taking large
amounts of time.

If I am wrong, could you help me by pointing me in the right direction?

Before I go, do you think any of the suggestions I made before would
work?

regardless - thanks for the help,

Ron

 

[Ragdyer]

It'll cost you as much as you spent here ... nothing!<g>

microsoft.public.excel.programming

I think you got here through google.
You can get there the same way.

Same bunch of nice folks, all helping for the sake of helping.