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)?
Element-wise operations on cell ranges in Google Spreadsheet
google spreadsheets
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.Edit: based on AdamL's confimation, this is a conclusive answer.