Excel - AVERAGE Function Problem - Expert Solution

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
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
Excelchat Expert 13/03/2018 - 03:16
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.