Confused as to what this "formula" does

[GrantW]

Using Excel 2010, by accident I ended up the following formula in a cell as follows:

=$A$1:$A$3

This seems to exhibit some strange behaviour that I cannot understand.

If this formula is anywhere on row 1, it returns the value in A1.
If it is on row 2, its returns the value in A2.
If it is on row 3 it returns the value in A3.
If it is on any other row it gives #VALUE.

I cannot work out what the formula is doing and why the outcome varies depending on what row it appears on.
Any explanations would be gratefully received.

Thanks

[Moo the Dog]

All it does is return the value of cells A1, A2 or A3, depending on what row you place the formula in. If you put it in row 4 or higher, it is 'out of range'.

- Moo

[shg]

The colon in Excel is the range operator, so that formula is referencing the 3-cell range A1:A3.

The reason it works the way it does in the first three rows is that Excel assumes an "implied intersection" -- you get the value of the cell in the same row as where the formula appears.

[GrantW]

OK thanks - implied intersection isn't something I had come across before

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[shg]

It also works with row references, so you could create a multiplication table with the multiplicands in the top row and left column.