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?

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