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?

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/showthread.php?t=96028