How to average each value in two separate sheets in excel into a third sheet

user_16040171 0
Viewed 37

Say I have an excel file with two sheets:

Sheet 1
    2    2    2
    4    4    4
Sheet 2
    0    0    0
    2    2    2

How would I take the average of each individual value from sheets 1 and 2 respecting their positions to produce a third sheet with averaged values like so?

Sheet 3
    1    1    1
    3    3    3

I am trying to follow this tutorial but that only produces one value in one cell for me. It appears to be taking the average of every number across two sheets and giving back one number)

xlsx excel average
1 Answers

Use AVERAGE with a 3d reference:

=AVERAGE(Sheet1:Sheet2!A1)

Note that Sheet1 and Sheet2 need to be next to each other and in that order.

enter image description here

If the sheets are not next to each other then:

=AVERAGE(Sheet1!A1,Sheet2!A1)
user_48515900
Related