|
Counting grades in questionaire
My problem: I have the survey data in an excel sheet, one respondent per row and one question per column. What i would like to do is show the number of grades per question (1-5) based on several criteria (the first 6 questions) for publishing on a company intranet. Could use a pivottable with reformatting of the data but the number of row would be extremely large (have 25000 respondents and 70 questions) Have tried using productsum, works but to slow for interactivity. Would be very grateful for tips of how to do this. Thinking of creating separate pivot tables for each question but that would mean a lot of tables :) /Johan -- johli ------------------------------------------------------------------------ johli's Profile: http://www.excelforum.com/member.php...o&userid=27388 View this thread: http://www.excelforum.com/showthread...hreadid=469120 |
At the bottom of your first column of grades, use 5 formulas like:
=COUNTIF(B2:B25000,1) =COUNTIF(B2:B25000,1) =COUNTIF(B2:B25000,1) =COUNTIF(B2:B25000,1) -- HTH, Bernie MS Excel MVP "johli" wrote in message ... My problem: I have the survey data in an excel sheet, one respondent per row and one question per column. What i would like to do is show the number of grades per question (1-5) based on several criteria (the first 6 questions) for publishing on a company intranet. Could use a pivottable with reformatting of the data but the number of row would be extremely large (have 25000 respondents and 70 questions) Have tried using productsum, works but to slow for interactivity. Would be very grateful for tips of how to do this. Thinking of creating separate pivot tables for each question but that would mean a lot of tables :) /Johan -- johli ------------------------------------------------------------------------ johli's Profile: http://www.excelforum.com/member.php...o&userid=27388 View this thread: http://www.excelforum.com/showthread...hreadid=469120 |
=COUNTIF(B2:B25000,1)
=COUNTIF(B2:B25000,1) -- HTH, Bernie MS Excel MVP "johli" wrote in message ... My problem: I have the survey data in an excel sheet, one respondent per row and one question per column. What i would like to do is show the number of grades per question (1-5) based on several criteria (the first 6 questions) for publishing on a company intranet. Could use a pivottable with reformatting of the data but the number of row would be extremely large (have 25000 respondents and 70 questions) Have tried using productsum, works but to slow for interactivity. Would be very grateful for tips of how to do this. Thinking of creating separate pivot tables for each question but that would mean a lot of tables :) /Johan -- johli ------------------------------------------------------------------------ johli's Profile: http://www.excelforum.com/member.php...o&userid=27388 View this thread: http://www.excelforum.com/showthread...hreadid=469120 |
Johan,
Aaargh, I have hit Ctrl-Enter TWICE by accident _twice_ now while pasting. My apologies. Anyway, at the bottom of column B, enter 5 formulas like =COUNTIF(B2:B25000,1) =COUNTIF(B2:B25000,2) =COUNTIF(B2:B25000,3) =COUNTIF(B2:B25000,4) =COUNTIF(B2:B25000,5) Adjust for the actual range. (These could also go at the top, if you insert 5 rows, and change the formulas to account for extra data: =COUNTIF(B7:B65000,1) Anyway, copy those formulas to the right (for the 70 other columns) and you will get a summary of the scores for each question. At least, I think that is what you are looking for. If not, post a SMALL sample of your data table, and what results you desire to produce. HTH, Bernie MS Excel MVP "johli" wrote in message ... My problem: I have the survey data in an excel sheet, one respondent per row and one question per column. What i would like to do is show the number of grades per question (1-5) based on several criteria (the first 6 questions) for publishing on a company intranet. Could use a pivottable with reformatting of the data but the number of row would be extremely large (have 25000 respondents and 70 questions) Have tried using productsum, works but to slow for interactivity. Would be very grateful for tips of how to do this. Thinking of creating separate pivot tables for each question but that would mean a lot of tables :) /Johan -- johli ------------------------------------------------------------------------ johli's Profile: http://www.excelforum.com/member.php...o&userid=27388 View this thread: http://www.excelforum.com/showthread...hreadid=469120 |
that or a frequency table would work great but the problem is that I would like to count only those rows that fullfill certain criteries. example Company Position Gender Q1 Q2 Q3 A 1 1 2 3 3 A 2 2 4 4 5 B 3 1 4 2 4 B 1 2 5 4 3 C 3 2 1 1 1 A 1 1 2 1 4 Based on the selection of a combination of Company and position for example count the respective number of grades for each question. like: Company A Position 1 Q1 Q2 Q3 1 0 1 0 2 2 0 0 3 0 1 1 4 0 0 1 5 0 0 0 /Johan -- johli ------------------------------------------------------------------------ johli's Profile: http://www.excelforum.com/member.php...o&userid=27388 View this thread: http://www.excelforum.com/showthread...hreadid=469120 |
Johan,
With your table starting in Row 10, with the Company of interest in cell A1, and the Position of interest in Cell B1, in cell D1 (Assuming Q1 is in column D), enter =SUMPRODUCT(($A$10:$A$25000=$A$1)*($B$10:$B$25000= $B$1)*(D$10:D$25000=ROW())) and copy down to cells D2:D5, then D1:D5 across to line up with any questions that you are interested in. HTH, Bernie MS Excel MVP "johli" wrote in message ... that or a frequency table would work great but the problem is that I would like to count only those rows that fullfill certain criteries. example Company Position Gender Q1 Q2 Q3 A 1 1 2 3 3 A 2 2 4 4 5 B 3 1 4 2 4 B 1 2 5 4 3 C 3 2 1 1 1 A 1 1 2 1 4 Based on the selection of a combination of Company and position for example count the respective number of grades for each question. like: Company A Position 1 Q1 Q2 Q3 1 0 1 0 2 2 0 0 3 0 1 1 4 0 0 1 5 0 0 0 /Johan -- johli ------------------------------------------------------------------------ johli's Profile: http://www.excelforum.com/member.php...o&userid=27388 View this thread: http://www.excelforum.com/showthread...hreadid=469120 |
Tried that but the problem here is performance problems, if this information should be calculated once it would be no problem but it is meant for the user to be able to select different criterias for comparison. Since sumproduct ends upp in A LOT of matrix multiplications it takes too long time.. /johan -- johli ------------------------------------------------------------------------ johli's Profile: http://www.excelforum.com/member.php...o&userid=27388 View this thread: http://www.excelforum.com/showthread...hreadid=469120 |
| All times are GMT +1. The time now is 05:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com