This article shows how to calculate average with intersecting tables using AVERAGE function and intersect operator, the space character (" ").
Syntax
=AVERAGE(first range second range)
Steps
- Type =AVERAGE( function
- Select or type the first range reference that includes data C5:H6
- Press Space key to add a space character
- Select or type the second range reference that includes data D3:E8
- Type ) to close function and finish the formula
How
The space character (" ") is an intersect operator in Excel. Putting a space between two ranges returns the intersection cell or range that the two ranges share. You can see how it works even by itself, if both intersecting ranges are single dimensional as well. For example;
=C5:H5 D3:D8 formula points D5 cell and returns the value of D5, 99.
If the intersection is a range of cells, it returns #VALUE! error if used without a function. However; it will work as a range if you use it inside a function that accepts ranges.
=AVERAGE(C5:H6 D3:E8) returns 309 that is the average of numbers in D5:E6.
If you try to intersect two not-intersecting ranges, e.g. C4:H4 C6:H6, the formula will return a #NULL! error.
