Question description:
This user has given permission to use the problem statement for this
blog.
I need to get a teach score average of their students. I have the students individual scores, I need to sort by their teachers.
Solved by S. W. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
13/03/2018 - 03:02
Hi, Welcome to Got It Pro.
User
13/03/2018 - 03:03
hello
Excelchat Expert
13/03/2018 - 03:03
Can you please provide sample data in the preview, that contain teacher, student and marks?
User
13/03/2018 - 03:04
it is two separate excel files
User
13/03/2018 - 03:04
one with the students and their scores, the other with the students and the teachers they are assigned to
Excelchat Expert
13/03/2018 - 03:04
Please provide data in seperate tabs and we can make in single file.
User
13/03/2018 - 03:05
oka
User
13/03/2018 - 03:05
y
User
13/03/2018 - 03:07
So the students in sheet 1 are assigned to different teachers. If the teachers have about 20 students, how can I look at the teachers overall averages?
Excelchat Expert
13/03/2018 - 03:08
You can actually, pull the teachers into the list where marks are present.
Excelchat Expert
13/03/2018 - 03:09
I am going to implement that.
Excelchat Expert
13/03/2018 - 03:09
Then, you can use pivot table to summarize by average.
User
13/03/2018 - 03:09
Also, there are some students that have multiple scores, and others that have no entries
Excelchat Expert
13/03/2018 - 03:10
Can you please provide the complete info to work and check the consequences?
User
13/03/2018 - 03:10
like copy and paste my data in here?
Excelchat Expert
13/03/2018 - 03:11
yes. That is right.
User
13/03/2018 - 03:11
I cannot, it is for internal use only.
User
13/03/2018 - 03:11
Can I try and expand on the examples that I have to give a better example
Excelchat Expert
13/03/2018 - 03:11
Please check the pivot table, i have created.
Excelchat Expert
13/03/2018 - 03:12
Once you update the data, it updates in the pivot table.
User
13/03/2018 - 03:12
yes
User
13/03/2018 - 03:12
how do I do that?
Excelchat Expert
13/03/2018 - 03:12
Add few more rows below.
Excelchat Expert
13/03/2018 - 03:12
In Sheet2, you can add data in columns A and B.
Excelchat Expert
13/03/2018 - 03:12
And copy the formula in column C.
Excelchat Expert
13/03/2018 - 03:13
Add student teacher data in sheet 1.
User
13/03/2018 - 03:14
Okay I am trying
User
13/03/2018 - 03:15
Will this work if Jane has 3 scores and automatically update that as well?
Excelchat Expert
13/03/2018 - 03:15
yes. it updates.
Excelchat Expert
13/03/2018 - 03:16
Please check. I have added 2 more rows of Jane.
Excelchat Expert
13/03/2018 - 03:16
and it is updaed under Teacher2.
User
13/03/2018 - 03:18
what kind of Vlookup do I do?
Excelchat Expert
13/03/2018 - 03:19
It is already applied in the column C.
Excelchat Expert
13/03/2018 - 03:19
Please copy the formula down, when you add new data.
User
13/03/2018 - 03:20
how do I apply the formula to all rows?
Excelchat Expert
13/03/2018 - 03:21
You can simply copy and paste to the rows down.
Excelchat Expert
13/03/2018 - 03:22
copy the cell using ctrl+c
Excelchat Expert
13/03/2018 - 03:22
Paste in the empty cell using ctrl+v
User
13/03/2018 - 03:23
oh I see my issue
Excelchat Expert
13/03/2018 - 03:24
What is the problem?
User
13/03/2018 - 03:25
I was using your formula straight form here but my tabs weren't labeled correctly
User
13/03/2018 - 03:25
so I have my top row correct now, how do I apply to the entire column
Excelchat Expert
13/03/2018 - 03:25
Ok.
Excelchat Expert
13/03/2018 - 03:26
You can copy the cell containing formula and paste down for all the rows.
Excelchat Expert
13/03/2018 - 03:26
But, my suggestion is to paste the formula till the last row.
Excelchat Expert
13/03/2018 - 03:26
where the data is available.
User
13/03/2018 - 03:27
That worked, I have a few showing #N/A
Excelchat Expert
13/03/2018 - 03:27
Because, there is no match.
User
13/03/2018 - 03:28
right okay, I see that it is because the spelling of student names
User
13/03/2018 - 03:28
well the I have what I need
Excelchat Expert
13/03/2018 - 03:28
Please check the names correctly.
Excelchat Expert
13/03/2018 - 03:29
Yes. Otherwise, this process works well.
User
13/03/2018 - 03:29
yes it worked for me
User
13/03/2018 - 03:29
thank you so much
Excelchat Expert
13/03/2018 - 03:29
When you are using it in Excel, please refresh the pivot table to see the latest data.
Excelchat Expert
13/03/2018 - 03:29
Thank you. Have wonderful day.
User
13/03/2018 - 03:29
yes that is working
User
13/03/2018 - 03:29
you too
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
