In Google Spreadsheets, what does one obtain if operations are performed on a range of cells? Have a look at the following example:

Intuitively, I would expect the result to be 14. This is the way Matlab and similar languages behave. They perform the operation on each element.

There are two questions:

• What is happening here? What exactly am I computing the average of?
• Is there a way to obtain the expected behavior (other than creating a new range of cells with values increased by 3)?
-

## migrated from superuser.comSep 3 '14 at 8:13

This question came from our site for computer enthusiasts and power users.

The short answer: To get the expected result, you should use `=AVERAGE(A1:E1) + 3`, not `=AVERAGE(A1:E1+3)`.

In effect, `A1:E1+3` is an expression that produces non-obvious results. Try inserting just `=A1:E1` in your `B3` cell, and it will return `4` (more on that below). Thus, `=A1:E1+3` is `7`, and `=AVERAGE(7)` is, well, `7`.

Another option is to use `=ARRAYFORMULA(A1:E1 + 3)`, which produces a new array where all elements are added with `3`, so that `=AVERAGE(ARRAYFORMULA(A1:E1 + 3))` gives `14`.

It seems that an expression that consists of only a range (`A1:E1`) will return the value of the range that corresponds to the current column. I can't find any documentation for this behaviour, so I'm just guessing based on some experimentation.

If entering `=A1:E1` in different cells. In `C3`, it returns `9`. In `D3`, it returns `16`.
Entering a range that does not include the current column, e.g. `=A1:B1` in column `C`, returns a `#VALUE` error.

Feel free to look at an example spreadsheet I used for experimentation.

Well, I feel I have answered the actual questions: a) "What is happening here? What exactly am I computing the average of?" - you're computing the average of `7`, because `A1:E1` is `4` and `4 + 3` is `7` and b) "Is there a way to obtain the expected behavior" - yes, by modifying the formula slightly, or by using `ARRAYFORMULA`. The part I can't explain is why `A1:E1` returns the element corresponding to the current cell, but that was not strictly part of the question. – Vidar S. Ramdal Sep 3 '14 at 10:40