Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is This a Pivot? A SumProduct? A SumPivot? Matrix?
Received a report from Excel, a survey. Sixteen respondents, each row of the
report is one answer to one question from one respondent. In other words: RESP | TOPIC | QUESTION | RESPONSE ================================= Adam | Envir. | Quest. 1 | 4 Adam | Envir. | Quest. 2 | 3 Adam | Mt'ls. | Quest. 3 | 4 Adam | Mt'ls. | Quest. 4 | 4 Brian | Envir. | Quest. 1 | 5 Brian | Envir. | Quest. 2 | 4 Brian | Mt'ls. | Quest. 3 | 4 Brian | Mt'ls. | Quest. 4 | 3 .... and so on, to respondent 'n'. I'd like a report that reads as follows: ENVIR. Quest. 1 A | 4 B | 5 .... n | 4 ==== Avg. 4.3 Quest. 2 A | 3 B | 4 .... n | 4 === Avg. 3.67 MAT'LS. Quest. 3 etc. etc. etc. I'm playing with in in Access, but also in Excel. I created a table that with respondents' ID's across the top (so each respondent is a column), ran the questions down column A (so each row is one question). That is, Cell B2 is person A's answer to Q1, C3 is person B's answer to Q2, Xn is person W's answer to Qn-1, etc. I was trying to figure out how I can write a formula for a cell that says, Look at the respondent's ID (in cell X$1), and populate his answer to question number n (question number in cell $An). I created a 'fake' Key for each row in my dataset, based on "=(Respondent ID & Question Number)". So I ran a VLOOKUP for (X$1&$An), but I really thought SumProduct would do the same thing. How do one find info based on two different cells in a matrix? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is This a Pivot? A SumProduct? A SumPivot? Matrix?
HI, one possibility would be using a Pivot Table, depending on what you want to do further RJB;343222 Wrote: Received a report from Excel, a survey. Sixteen respondents, each row of the report is one answer to one question from one respondent. In other words: RESP | TOPIC | QUESTION | RESPONSE ================================= Adam | Envir. | Quest. 1 | 4 Adam | Envir. | Quest. 2 | 3 Adam | Mt'ls. | Quest. 3 | 4 Adam | Mt'ls. | Quest. 4 | 4 Brian | Envir. | Quest. 1 | 5 Brian | Envir. | Quest. 2 | 4 Brian | Mt'ls. | Quest. 3 | 4 Brian | Mt'ls. | Quest. 4 | 3 .... and so on, to respondent 'n'. I'd like a report that reads as follows: ENVIR. Quest. 1 A | 4 B | 5 .... n | 4 ==== Avg. 4.3 Quest. 2 A | 3 B | 4 .... n | 4 === Avg. 3.67 MAT'LS. Quest. 3 etc. etc. etc. I'm playing with in in Access, but also in Excel. I created a table that with respondents' ID's across the top (so each respondent is a column), ran the questions down column A (so each row is one question). That is, Cell B2 is person A's answer to Q1, C3 is person B's answer to Q2, Xn is person W's answer to Qn-1, etc. I was trying to figure out how I can write a formula for a cell that says, Look at the respondent's ID (in cell X$1), and populate his answer to question number n (question number in cell $An). I created a 'fake' Key for each row in my dataset, based on "=(Respondent ID & Question Number)". So I ran a VLOOKUP for (X$1&$An), but I really thought SumProduct would do the same thing. How do one find info based on two different cells in a matrix? -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=96028 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I transpose nXm matrix to mXn Matrix | Excel Worksheet Functions | |||
Use SUMPRODUCT for a matrix | Excel Worksheet Functions | |||
sumproduct in pivot | Excel Discussion (Misc queries) | |||
Matrix multiplication using sumproduct | Excel Worksheet Functions | |||
Pivot, Sumproduct or both? | Excel Worksheet Functions |