The short answer: To get the expected result, you should use
=AVERAGE(A1:E1) + 3, not
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,
=AVERAGE(7) is, well,
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
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.
=A1:E1 in different cells. In
C3, it returns
D3, it returns
Entering a range that does not include the current column, e.g.
=A1:B1 in column
C, returns a
Feel free to look at an example spreadsheet I used for experimentation.
Edit: based on AdamL's confimation, this is a conclusive answer.