Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
johli
 
Posts: n/a
Default 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

  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #3   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

=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



  #4   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #5   Report Post  
johli
 
Posts: n/a
Default


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



  #6   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #7   Report Post  
johli
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting rows, then counting values. Michael via OfficeKB.com Excel Discussion (Misc queries) 7 August 4th 05 11:57 PM
Counting number of grades in a row Marie1uk Excel Worksheet Functions 13 July 6th 05 01:56 PM
Vlookup student grades Vicky Excel Discussion (Misc queries) 7 May 19th 05 02:19 PM
Converting Letter Grades to Numeric Angelo D Excel Worksheet Functions 6 April 25th 05 08:29 PM
Counting... Patrick G Excel Worksheet Functions 3 February 23rd 05 11:05 PM


All times are GMT +1. The time now is 08:43 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"