Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data listed Vertically into Horizontal Table
First, I have no idea how I would search for this question, so if it has been
asked and someone would point me to it, I'll look there. Here is my problem: I have an Excel File "Test Data". I have another Excel File "Assessment Results". The "Test Data" file is imported from a testing program, which automatically scores multiple choice tests. It lists the result of a each student's answer to each question in vertical rows, like this: Name Question Points Earned Student, A 1 1 Student, A 2 0 And so on for each student. My "Assessment Results" file is structured like this: Name Question #1 Question #2 Student, A 1 0 Student, B 0 1 So, I would like to be able to copy and paste the formula and have it correctly move down the vertical list in "Test Data" when it autoupdates the formula, rather than have to show Excel where to find the correct cell in "Test Data" for every single student & question (that's 17 questions x 14 students for one class, alone). I hope this makes sense... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data listed Vertically into Horizontal Table
Instead of using labels "Question #1", use a label of just "1" (although you
could insert a row above saying question, for aesthetics. Also, have both files opened when you do this, will hlep Excel find the correct file you are referring to. Assuming first question score goes into C2. =SUMPRODUCT(('[Test Data.xls]Sheet1'!$A$2:$A$250=$A2)*('[Test Data.xls]Sheet1'!$B$2:$B$250=B$1)*('[Test Data.xls]Sheet1'!$C$2:$C$250)) Of course, change Sheet1 to whatever you have the sheet named in your Test Data file. You should then be able to copy this down and across as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "SeventFloorProfessor" wrote: First, I have no idea how I would search for this question, so if it has been asked and someone would point me to it, I'll look there. Here is my problem: I have an Excel File "Test Data". I have another Excel File "Assessment Results". The "Test Data" file is imported from a testing program, which automatically scores multiple choice tests. It lists the result of a each student's answer to each question in vertical rows, like this: Name Question Points Earned Student, A 1 1 Student, A 2 0 And so on for each student. My "Assessment Results" file is structured like this: Name Question #1 Question #2 Student, A 1 0 Student, B 0 1 So, I would like to be able to copy and paste the formula and have it correctly move down the vertical list in "Test Data" when it autoupdates the formula, rather than have to show Excel where to find the correct cell in "Test Data" for every single student & question (that's 17 questions x 14 students for one class, alone). I hope this makes sense... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data listed Vertically into Horizontal Table
=SUMPRODUCT(--('[Test Data]Sheet1!$A1:$A5000=$A2),--('[Test
Data]Sheet1!$B1:$B5000=B$1),'[Test Data]Sheet1!$C1:$C5000) Will work assuming test data is the name of the other file, and info is in a sheet named Sheet1. also assumes rep names arei n column A and other info in columns B and right. This formula goes in B2 and can be copied and pasted to the bottom and right. "SeventFloorProfessor" wrote: First, I have no idea how I would search for this question, so if it has been asked and someone would point me to it, I'll look there. Here is my problem: I have an Excel File "Test Data". I have another Excel File "Assessment Results". The "Test Data" file is imported from a testing program, which automatically scores multiple choice tests. It lists the result of a each student's answer to each question in vertical rows, like this: Name Question Points Earned Student, A 1 1 Student, A 2 0 And so on for each student. My "Assessment Results" file is structured like this: Name Question #1 Question #2 Student, A 1 0 Student, B 0 1 So, I would like to be able to copy and paste the formula and have it correctly move down the vertical list in "Test Data" when it autoupdates the formula, rather than have to show Excel where to find the correct cell in "Test Data" for every single student & question (that's 17 questions x 14 students for one class, alone). I hope this makes sense... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data listed Vertically into Horizontal Table
I don't think I was very clear, because I provided too much information. You
may still be correct in what I should do. File 1: "Test Data" Sheet: "Test Scores" File 2: "Assessment Results" Sheet: "Enter Results" The "Test Data" file has a chart with the student's name and whether or not they earned a point for each of 17 questions, like this (I included the "Row" here to help; it's not actually a column in my file. The number in () after each column heading denotes the column it is in.): Row Name (A) Point Earned (C) 2 Student, A 1 3 Student, A 1 <skip to next student 19 Student, B 1 20 Student, B 0 <skip to next student 36 Student, C 1 37 Student, C 1 <and so on... The file "Assessment Results" that I use to assess the data is all set up with student names, the formulas I need to analyze scores, etc. It looks like this (I included the "Row" and "Column" here to help; it's not actually a column in my file): Row Name (B) 1(C) 2(D) 3(E) <to 17(V) 4 Student, A 1 1 0 5 Student, B 1 0 0 6 Student, C 1 1 1 I would like to enter a formula in "Assessment Result" C4 that copies the value in "Test Data" C2. Then, when I copy it, "Assessment Result" D4 would have the value from "Test Data" C3, and so on, until "Student, A" scores are taken from "Test Data" C2 to C18. The problem is that "Test Data" lists the values vertically, and "Assessment Results" lists them horizontally, so Excel won't autoupdate the way I'd like it to! Also, I don't know how to tell it to autoupdate so that the value for "Student, B" on "Assessment Results" C5 to V5 begins on "Test Data" C19 and continues to C35 (again, because "Test Data" lists the seventeen values vertically). I know this is confusing. I'm just trying to spend less time grading and entering data, and more time analyzing data. Thanks! "Sean Timmons" wrote: =SUMPRODUCT(--('[Test Data]Sheet1!$A1:$A5000=$A2),--('[Test Data]Sheet1!$B1:$B5000=B$1),'[Test Data]Sheet1!$C1:$C5000) Will work assuming test data is the name of the other file, and info is in a sheet named Sheet1. also assumes rep names arei n column A and other info in columns B and right. This formula goes in B2 and can be copied and pasted to the bottom and right. "SeventFloorProfessor" wrote: First, I have no idea how I would search for this question, so if it has been asked and someone would point me to it, I'll look there. Here is my problem: I have an Excel File "Test Data". I have another Excel File "Assessment Results". The "Test Data" file is imported from a testing program, which automatically scores multiple choice tests. It lists the result of a each student's answer to each question in vertical rows, like this: Name Question Points Earned Student, A 1 1 Student, A 2 0 And so on for each student. My "Assessment Results" file is structured like this: Name Question #1 Question #2 Student, A 1 0 Student, B 0 1 So, I would like to be able to copy and paste the formula and have it correctly move down the vertical list in "Test Data" when it autoupdates the formula, rather than have to show Excel where to find the correct cell in "Test Data" for every single student & question (that's 17 questions x 14 students for one class, alone). I hope this makes sense... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data listed Vertically into Horizontal Table
No problems! :-)
the formula from below still appies. Just need to change the cell references to match. =SUMPRODUCT(--('[Test Data]TestScores'!$A$1:$A$5000=$B4),--('[Test Data]TestScores'!$B$1:$B$5000=B$1),'[Test Data]Test Scores'!$C$1:$C$5000) "SeventFloorProfessor" wrote: I don't think I was very clear, because I provided too much information. You may still be correct in what I should do. File 1: "Test Data" Sheet: "Test Scores" File 2: "Assessment Results" Sheet: "Enter Results" The "Test Data" file has a chart with the student's name and whether or not they earned a point for each of 17 questions, like this (I included the "Row" here to help; it's not actually a column in my file. The number in () after each column heading denotes the column it is in.): Row Name (A) Point Earned (C) 2 Student, A 1 3 Student, A 1 <skip to next student 19 Student, B 1 20 Student, B 0 <skip to next student 36 Student, C 1 37 Student, C 1 <and so on... The file "Assessment Results" that I use to assess the data is all set up with student names, the formulas I need to analyze scores, etc. It looks like this (I included the "Row" and "Column" here to help; it's not actually a column in my file): Row Name (B) 1(C) 2(D) 3(E) <to 17(V) 4 Student, A 1 1 0 5 Student, B 1 0 0 6 Student, C 1 1 1 I would like to enter a formula in "Assessment Result" C4 that copies the value in "Test Data" C2. Then, when I copy it, "Assessment Result" D4 would have the value from "Test Data" C3, and so on, until "Student, A" scores are taken from "Test Data" C2 to C18. The problem is that "Test Data" lists the values vertically, and "Assessment Results" lists them horizontally, so Excel won't autoupdate the way I'd like it to! Also, I don't know how to tell it to autoupdate so that the value for "Student, B" on "Assessment Results" C5 to V5 begins on "Test Data" C19 and continues to C35 (again, because "Test Data" lists the seventeen values vertically). I know this is confusing. I'm just trying to spend less time grading and entering data, and more time analyzing data. Thanks! "Sean Timmons" wrote: =SUMPRODUCT(--('[Test Data]Sheet1!$A1:$A5000=$A2),--('[Test Data]Sheet1!$B1:$B5000=B$1),'[Test Data]Sheet1!$C1:$C5000) Will work assuming test data is the name of the other file, and info is in a sheet named Sheet1. also assumes rep names arei n column A and other info in columns B and right. This formula goes in B2 and can be copied and pasted to the bottom and right. "SeventFloorProfessor" wrote: First, I have no idea how I would search for this question, so if it has been asked and someone would point me to it, I'll look there. Here is my problem: I have an Excel File "Test Data". I have another Excel File "Assessment Results". The "Test Data" file is imported from a testing program, which automatically scores multiple choice tests. It lists the result of a each student's answer to each question in vertical rows, like this: Name Question Points Earned Student, A 1 1 Student, A 2 0 And so on for each student. My "Assessment Results" file is structured like this: Name Question #1 Question #2 Student, A 1 0 Student, B 0 1 So, I would like to be able to copy and paste the formula and have it correctly move down the vertical list in "Test Data" when it autoupdates the formula, rather than have to show Excel where to find the correct cell in "Test Data" for every single student & question (that's 17 questions x 14 students for one class, alone). I hope this makes sense... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data listed Vertically into Horizontal Table
Okay, I will play with it some... a couple of questions...
Does it matter if my "array" is an excel file, and not a data file? What are the "--"? "Sean Timmons" wrote: No problems! :-) the formula from below still appies. Just need to change the cell references to match. =SUMPRODUCT(--('[Test Data]TestScores'!$A$1:$A$5000=$B4),--('[Test Data]TestScores'!$B$1:$B$5000=B$1),'[Test Data]Test Scores'!$C$1:$C$5000) "SeventFloorProfessor" wrote: I don't think I was very clear, because I provided too much information. You may still be correct in what I should do. File 1: "Test Data" Sheet: "Test Scores" File 2: "Assessment Results" Sheet: "Enter Results" The "Test Data" file has a chart with the student's name and whether or not they earned a point for each of 17 questions, like this (I included the "Row" here to help; it's not actually a column in my file. The number in () after each column heading denotes the column it is in.): Row Name (A) Point Earned (C) 2 Student, A 1 3 Student, A 1 <skip to next student 19 Student, B 1 20 Student, B 0 <skip to next student 36 Student, C 1 37 Student, C 1 <and so on... The file "Assessment Results" that I use to assess the data is all set up with student names, the formulas I need to analyze scores, etc. It looks like this (I included the "Row" and "Column" here to help; it's not actually a column in my file): Row Name (B) 1(C) 2(D) 3(E) <to 17(V) 4 Student, A 1 1 0 5 Student, B 1 0 0 6 Student, C 1 1 1 I would like to enter a formula in "Assessment Result" C4 that copies the value in "Test Data" C2. Then, when I copy it, "Assessment Result" D4 would have the value from "Test Data" C3, and so on, until "Student, A" scores are taken from "Test Data" C2 to C18. The problem is that "Test Data" lists the values vertically, and "Assessment Results" lists them horizontally, so Excel won't autoupdate the way I'd like it to! Also, I don't know how to tell it to autoupdate so that the value for "Student, B" on "Assessment Results" C5 to V5 begins on "Test Data" C19 and continues to C35 (again, because "Test Data" lists the seventeen values vertically). I know this is confusing. I'm just trying to spend less time grading and entering data, and more time analyzing data. Thanks! "Sean Timmons" wrote: =SUMPRODUCT(--('[Test Data]Sheet1!$A1:$A5000=$A2),--('[Test Data]Sheet1!$B1:$B5000=B$1),'[Test Data]Sheet1!$C1:$C5000) Will work assuming test data is the name of the other file, and info is in a sheet named Sheet1. also assumes rep names arei n column A and other info in columns B and right. This formula goes in B2 and can be copied and pasted to the bottom and right. "SeventFloorProfessor" wrote: First, I have no idea how I would search for this question, so if it has been asked and someone would point me to it, I'll look there. Here is my problem: I have an Excel File "Test Data". I have another Excel File "Assessment Results". The "Test Data" file is imported from a testing program, which automatically scores multiple choice tests. It lists the result of a each student's answer to each question in vertical rows, like this: Name Question Points Earned Student, A 1 1 Student, A 2 0 And so on for each student. My "Assessment Results" file is structured like this: Name Question #1 Question #2 Student, A 1 0 Student, B 0 1 So, I would like to be able to copy and paste the formula and have it correctly move down the vertical list in "Test Data" when it autoupdates the formula, rather than have to show Excel where to find the correct cell in "Test Data" for every single student & question (that's 17 questions x 14 students for one class, alone). I hope this makes sense... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referencing Data listed Vertically into Horizontal Table
Also, I finally wised up and copied and pasted the information from file
"Test Data" into file "Assessment Results" as a new sheet, "Data". I don't know if this changes anything... "Sean Timmons" wrote: No problems! :-) the formula from below still appies. Just need to change the cell references to match. =SUMPRODUCT(--('[Test Data]TestScores'!$A$1:$A$5000=$B4),--('[Test Data]TestScores'!$B$1:$B$5000=B$1),'[Test Data]Test Scores'!$C$1:$C$5000) "SeventFloorProfessor" wrote: I don't think I was very clear, because I provided too much information. You may still be correct in what I should do. File 1: "Test Data" Sheet: "Test Scores" File 2: "Assessment Results" Sheet: "Enter Results" The "Test Data" file has a chart with the student's name and whether or not they earned a point for each of 17 questions, like this (I included the "Row" here to help; it's not actually a column in my file. The number in () after each column heading denotes the column it is in.): Row Name (A) Point Earned (C) 2 Student, A 1 3 Student, A 1 <skip to next student 19 Student, B 1 20 Student, B 0 <skip to next student 36 Student, C 1 37 Student, C 1 <and so on... The file "Assessment Results" that I use to assess the data is all set up with student names, the formulas I need to analyze scores, etc. It looks like this (I included the "Row" and "Column" here to help; it's not actually a column in my file): Row Name (B) 1(C) 2(D) 3(E) <to 17(V) 4 Student, A 1 1 0 5 Student, B 1 0 0 6 Student, C 1 1 1 I would like to enter a formula in "Assessment Result" C4 that copies the value in "Test Data" C2. Then, when I copy it, "Assessment Result" D4 would have the value from "Test Data" C3, and so on, until "Student, A" scores are taken from "Test Data" C2 to C18. The problem is that "Test Data" lists the values vertically, and "Assessment Results" lists them horizontally, so Excel won't autoupdate the way I'd like it to! Also, I don't know how to tell it to autoupdate so that the value for "Student, B" on "Assessment Results" C5 to V5 begins on "Test Data" C19 and continues to C35 (again, because "Test Data" lists the seventeen values vertically). I know this is confusing. I'm just trying to spend less time grading and entering data, and more time analyzing data. Thanks! "Sean Timmons" wrote: =SUMPRODUCT(--('[Test Data]Sheet1!$A1:$A5000=$A2),--('[Test Data]Sheet1!$B1:$B5000=B$1),'[Test Data]Sheet1!$C1:$C5000) Will work assuming test data is the name of the other file, and info is in a sheet named Sheet1. also assumes rep names arei n column A and other info in columns B and right. This formula goes in B2 and can be copied and pasted to the bottom and right. "SeventFloorProfessor" wrote: First, I have no idea how I would search for this question, so if it has been asked and someone would point me to it, I'll look there. Here is my problem: I have an Excel File "Test Data". I have another Excel File "Assessment Results". The "Test Data" file is imported from a testing program, which automatically scores multiple choice tests. It lists the result of a each student's answer to each question in vertical rows, like this: Name Question Points Earned Student, A 1 1 Student, A 2 0 And so on for each student. My "Assessment Results" file is structured like this: Name Question #1 Question #2 Student, A 1 0 Student, B 0 1 So, I would like to be able to copy and paste the formula and have it correctly move down the vertical list in "Test Data" when it autoupdates the formula, rather than have to show Excel where to find the correct cell in "Test Data" for every single student & question (that's 17 questions x 14 students for one class, alone). I hope this makes sense... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Horizontal Data arranged Vertically | Excel Discussion (Misc queries) | |||
Lookdown cell vertically, return horizontal value | Excel Discussion (Misc queries) | |||
Does how #s are listed affect IRR calc- same #s vertically vs. hor | Excel Worksheet Functions | |||
How do I link horizontal cells vertically? | Excel Discussion (Misc queries) | |||
I would like the tabs in Excel to be listed vertically on left. | Excel Discussion (Misc queries) |