Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

Example: Adding 3 to a range

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)?
share|improve this question

migrated from superuser.com Sep 3 at 8:13

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

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
Excel behaves the same way. Array entry would seem to meet OP's expectation. –  pnuts Sep 3 at 10:01
    
I can't find any documentation for this behaviour. Do you know any? –  Vidar S. Ramdal Sep 3 at 10:03
    
I have seen @Barry Houdini (probably) mention this (on SO or perhaps SU) (for Excel) and I think he used a term that would now be useful for searching but I regret it was a while ago and I have forgotten. I don't recall mention in official documentation (but have not looked there). –  pnuts Sep 3 at 10:06
2  
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 at 10:40
3  
I can't explain the behaviour either Jacob, other than to say it is what it is. In all spreadsheet applications that I know of, if you reference a range when a single value is expected, and an array calculation enabler has not been used, then the value in the corresponding row or column is used (or a #VALUE error if there is no corresponding row or column). It would be like asking "why do SUMPRODUCT and LOOKUP not require Ctrl+Shift+Enter to perform array calculations in Excel?" I don't know the answer why... they just don't. –  AdamL Sep 3 at 11:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.