Question description:
This user has given permission to use the problem statement for this
blog.
Is there a formula to find a mean or median of the same column in multiple (â?¥200) sheets?
Solved by S. Q. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/07/2018 - 02:32
hello...
User
06/07/2018 - 02:32
Not the mean of the means, btw
Excelchat Expert
06/07/2018 - 02:33
Could you explain your problem...??
User
06/07/2018 - 02:33
We have over 200 sheets with student responses rating teacher performance. We'd like to know the overall average, not average by teacher.
User
06/07/2018 - 02:34
Different number of students in each sheet
User
06/07/2018 - 02:34
Merging the sheets is too large a task
Excelchat Expert
06/07/2018 - 02:34
all sheets in one workbook..??
User
06/07/2018 - 02:35
I think so. I'm not the person doing the compiling
Excelchat Expert
06/07/2018 - 02:35
all data is in the same column...??
User
06/07/2018 - 02:35
(All in Google Sheets, but he knows how to export to Excel)
User
06/07/2018 - 02:35
Yes
User
06/07/2018 - 02:35
And I think it's numeric
Excelchat Expert
06/07/2018 - 02:36
hold on..
User
06/07/2018 - 02:36
("Rate your teacher on a scale of 1-4", e.g.)
Excelchat Expert
06/07/2018 - 02:39
[Uploaded an Excel file]
Excelchat Expert
06/07/2018 - 02:39
this is example...
Excelchat Expert
06/07/2018 - 02:40
=AVERAGE(Sheet1:Sheet3!B1:B9)
Excelchat Expert
06/07/2018 - 02:41
it will give you average from sheet1 through sheet3.. in this case..
Excelchat Expert
06/07/2018 - 02:41
if you have 200 sheets.. you can just select 1st sheet to 200th sheet.
User
06/07/2018 - 02:43
Okay, turns out it's non-numeric. Is there a way to do find/replace over an entire workbook?
Excelchat Expert
06/07/2018 - 02:43
what you mean non-numeric
Excelchat Expert
06/07/2018 - 02:44
1 -4 is text..??
User
06/07/2018 - 02:45
No. Turns out we'd need to find/replace all "strongly agree" with 4, "agree" with 3, "disagree" with 2, "strongly disagree" with 1
User
06/07/2018 - 02:45
Easily enough done on one sheet, but how about multiple?
Excelchat Expert
06/07/2018 - 02:46
hold on..
User
06/07/2018 - 02:46
nvm... It's right on find/replace to do the entire workbook!
User
06/07/2018 - 02:46
Thanks a bunch!
Excelchat Expert
06/07/2018 - 02:46
no problem..
User
06/07/2018 - 02:46
Wait... stupid followup
Excelchat Expert
06/07/2018 - 02:47
ok..
User
06/07/2018 - 02:47
Was that solution for Excel only, or would it work for Google too?
Excelchat Expert
06/07/2018 - 02:47
google sheet and excel work slightly different way..
Excelchat Expert
06/07/2018 - 02:47
it usually works..
Excelchat Expert
06/07/2018 - 02:48
you can try it google sheet first..
Excelchat Expert
06/07/2018 - 02:48
if not ,, you can just download in excel..
User
06/07/2018 - 02:49
Thx!
Excelchat Expert
06/07/2018 - 02:49
no problem..
Excelchat Expert
06/07/2018 - 02:49
do you have any other questions...??
User
06/07/2018 - 02:50
no
User
06/07/2018 - 02:50
bye
Excelchat Expert
06/07/2018 - 02:50
have good day...
Excelchat Expert
06/07/2018 - 02:51
Don't forget to give additional feedback to help us improve this service!
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.
