Excel help, please

spawnsharks · 08-27-2007, 03:52 PM

I need to make a spreadsheet to track a poll.... there are ten questions and most yes/no answers, but some up to 6 answer options, with "all that apply" responses.

I've done simple charting, but am semi-stumpped as how to set this up to be able to easily input results from a stack of responses, entering data, then be able to pull up a report of how many "a" answers from question 1 etc...

Help?

Questions 1-10, answers a-f. Seemed easy enough to go questions across, answers up and down, but then input became an issue....

Kindred · 08-27-2007, 04:24 PM

Mmm....you lost me a little bit there, but it SOUNDS like the function you're looking for is "countif". See the screen shot below.

The formula is visible in the formula bar up top, in this case, =CountIF(B1:B6,"yes") will count all instancesof the word "yes".

countif.jpg

spawnsharks · 08-27-2007, 08:01 PM

What I really need to do, I realize, is make a "tally" in the response cells... the chart will look like this... (barring a better suggestion)

Question 1 Question 2 Question 3 Question4...

Answer a

Answer b

Answer c

Answer d

Answer e

So, as I have a stack of filled out questionaires, I need an easy way to simply tally the responses without manually doing the addition in each cell. Unless you can see an easier way of doing it.

Like I said... about 100 questionaires, ten multiple choice questions each, with anywhere from 2 to 6 answers (some with multiple responses allowed)

Then, chart the data from the tallies... question 1: X yes, x no. Question 2: x a, x b, x c etc....

Help is greatly appreciated and rewarded with 1,000 Internets.

Kindred · 08-28-2007, 02:43 PM

same formula...just pivot it.

countif2.jpg

BoBo · 08-28-2007, 03:00 PM

My reading is that you need to compile the final info, not a running record i.e. 17 people said answer b to question 2. You don't need person f answered answer b to question 2 - correct?

If so, run a manual tally, it's a lot faster.

secretweaponevan · 08-28-2007, 07:21 PM

AFAIK, any workbook can only be opened by 1 user at a time, so if you are looking at distributing access to your survey, a database might be a better solution, especially since it sounds to me that you are looking to tabulate data.

As for using Excel, you could have a survey worksheet and have each question's answer stored on a seperate worksheet in the workbook. Write a macro that inputs the answer (a, b, c, d...) into the first blank cell starting at A2, B2, C2, etc. and use A1, B1, C1, etc to sum the column.

Just waking up, need more coffee.

Kindred · 08-28-2007, 10:06 PM

Better yet, distribute it on a webpage so you can have the results dumped right into a .csv file! No typing into Excel!

08-27-2007, 03:52 PM	#1 (permalink)
spawnsharks Newbie Join Date: Feb 2007 Location: San Francisco, CA	Excel help, please I need to make a spreadsheet to track a poll.... there are ten questions and most yes/no answers, but some up to 6 answer options, with "all that apply" responses. I've done simple charting, but am semi-stumpped as how to set this up to be able to easily input results from a stack of responses, entering data, then be able to pull up a report of how many "a" answers from question 1 etc... Help? Questions 1-10, answers a-f. Seemed easy enough to go questions across, answers up and down, but then input became an issue.... __________________ Boomerangs are pretty much Frisbees for antisocial kids I don't understand how hitting someone with one of 300 balls, fired at 30BPS is skill.... yet a single ball to the face from a PGP is a "lucky" shot.... My gallery Classic Nickel 15rd PMI-1 for trade... HERE If we've dealt in the past, please leave feedback in my Feedback thread

08-27-2007, 04:24 PM	#2 (permalink)
Kindred Mod & Underwear Model Join Date: Mar 2006 Location: Neshanic Station, NJ	Mmm....you lost me a little bit there, but it SOUNDS like the function you're looking for is "countif". See the screen shot below. The formula is visible in the formula bar up top, in this case, =CountIF(B1:B6,"yes") will count all instancesof the word "yes". countif.jpg __________________ Strive for that moment when you're only a slice of pizza and a hooker away from paradise.

08-27-2007, 08:01 PM	#3 (permalink)
spawnsharks Newbie Join Date: Feb 2007 Location: San Francisco, CA	What I really need to do, I realize, is make a "tally" in the response cells... the chart will look like this... (barring a better suggestion) Question 1 Question 2 Question 3 Question4... Answer a Answer b Answer c Answer d Answer e So, as I have a stack of filled out questionaires, I need an easy way to simply tally the responses without manually doing the addition in each cell. Unless you can see an easier way of doing it. Like I said... about 100 questionaires, ten multiple choice questions each, with anywhere from 2 to 6 answers (some with multiple responses allowed) Then, chart the data from the tallies... question 1: X yes, x no. Question 2: x a, x b, x c etc.... Help is greatly appreciated and rewarded with 1,000 Internets. __________________ Boomerangs are pretty much Frisbees for antisocial kids I don't understand how hitting someone with one of 300 balls, fired at 30BPS is skill.... yet a single ball to the face from a PGP is a "lucky" shot.... My gallery Classic Nickel 15rd PMI-1 for trade... HERE If we've dealt in the past, please leave feedback in my Feedback thread

08-28-2007, 02:43 PM	#4 (permalink)
Kindred Mod & Underwear Model Join Date: Mar 2006 Location: Neshanic Station, NJ	same formula...just pivot it. countif2.jpg __________________ Strive for that moment when you're only a slice of pizza and a hooker away from paradise.

08-28-2007, 03:00 PM	#5 (permalink)
BoBo Active Member Join Date: Apr 2006 Location: SF Bay Area	My reading is that you need to compile the final info, not a running record i.e. 17 people said answer b to question 2. You don't need person f answered answer b to question 2 - correct? If so, run a manual tally, it's a lot faster. __________________ Say what now?

08-28-2007, 07:21 PM	#6 (permalink)
secretweaponevan Corn-on-the-cob champion! Join Date: Aug 2007 Location: Summerfield, FL	AFAIK, any workbook can only be opened by 1 user at a time, so if you are looking at distributing access to your survey, a database might be a better solution, especially since it sounds to me that you are looking to tabulate data. As for using Excel, you could have a survey worksheet and have each question's answer stored on a seperate worksheet in the workbook. Write a macro that inputs the answer (a, b, c, d...) into the first blank cell starting at A2, B2, C2, etc. and use A1, B1, C1, etc to sum the column. Just waking up, need more coffee.

08-28-2007, 10:06 PM	#7 (permalink)
Kindred Mod & Underwear Model Join Date: Mar 2006 Location: Neshanic Station, NJ	Better yet, distribute it on a webpage so you can have the results dumped right into a .csv file! No typing into Excel! __________________ Strive for that moment when you're only a slice of pizza and a hooker away from paradise.