Cell set equal to anchored ranges returns only one value in that range....why?
I've used this formula before but I don't really understand why it gives the result as such. I understand how anchors work ($A anchors the COLUMN and $2 anchors the ROW). However I'm still trying to understand if the cell is equal to a range, how come it displays the same number? In other words, the range B2:B6 all equal $A$2:$A
Why does this happen?
Does the cell evaluate the value of $A$2:$A(current row)? If so, shouldn't this yield a range rather than one specific number?
Thanks for the help
The main reason is because you are using a non-array function and therefore only single values will be passed. The same will happen if you use A2:A
. Only one value (the first one corresponding to 10) will be displayed.
If you want to reference a range in a cell you should use an array formula to be able to populate all the cells of that range. You can easily achieve this by using ARRAYFORMULA as follows:
=ARRAYFORMULA($A$2:$A)