What happens when I refer to a named range?

**RogeratCCCC** · 04-21-2012, 12:29 PM

I’m confused by what happens when I refer to a named range.
For example, I entered four integers as follows:
A1=1, A2=2, A3=3, A4=4,
then defined a named range “xrange” as these four cells - A1, A2, A3, A4.

Then if type “=xrange” into cell B2 and press enter, then the displayed result is 2.
But if I type “=xrange” into cell B2, and then press Ctrl-Shift-Enter (for an array formula), then the displayed result is 1.

Why does this difference exist, and how is the processing different in this situation for an array formula as opposed to a non-array formula? (I’m using Excel 2003)

( I encountered this problem as I was working on a complex array formula, then realized I wasn’t sure what was happening in the background array processing.)

Thanks in advance for any explanation.

**shg** · 04-21-2012, 01:04 PM

When you enter =xrange in B2 and confirm normally using Enter, there is an "implied intersection" -- the formula gets the value of the array that is in the same row.

If the array were in A1:D1, you would get the value in same column.

If you selected four cells in a column (starting in any row) and array-entered the formula, you would see all four values.

You could create two such ranges, one as a row header and one as a column header, and create a multiplication table in the body: =xrange*yrange

It's one of those things in Excel that just 'works', and it isn't until you look closely that you see how clever it is.

**RogeratCCCC** · 04-21-2012, 02:06 PM

Thanks, SHG, your response is very helpful, and I understand the situation better, I think. Now a followup question: is there any way to specify that an array formula should work on only one (or the first) value of an array for debugging purposes? I was having trouble with a complicated array formula, so I was trying to use it on just one array value to see if the calculation was correct, but then I ran into the problem of how to specify just one value in an array that is already defined with a named range. If you have suggestions on that, I would appreciate it. Thanks again.