**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.*