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.