Relative references in arrays
 

Can anyone answer a question: why is it that if I select a range, type a
formula which contains a relative reference, then hit ctrl+enter, the
reference contained in each cell will indeed vary according to its position
in the range. However, if I hit ctrl+shift+enter, to enter an array, all
cells will contain the same literal reference I typed in. What is the
rationale here? Perhaps it would help me understand a bit more about arrays.

Relative references in arrays
 

In the first case, you are applying the formula to each cell, telling Excel
to return the value of that single cell formula to that cell (and Excel
updates it relative to the cell).

In the second, case, you are applying the formula to the BLOCK of cells, and
are telling Excel to return the array of results to each cell in turn
(either truncating, or erroring if the block is too small or too large).

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"hmm" wrote in message
...
Can anyone answer a question: why is it that if I select a range, type a
formula which contains a relative reference, then hit ctrl+enter, the
reference contained in each cell will indeed vary according to its
position
in the range. However, if I hit ctrl+shift+enter, to enter an array, all
cells will contain the same literal reference I typed in. What is the
rationale here? Perhaps it would help me understand a bit more about
arrays.