Formulas as names : limitations?

[nunoferreira]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC

The following is a simplified version of a
problem I am having concerning names that stand
for formulas when the formulas refer
themselves to other names:

A1:A25 contain numbers. I have named this range
"originalValues"

I need B1:B25 to contain values that are a
function of the corresponding values in A1:A25.
So I filled-in each cell of B1:B25 with
"=2*originalValues"

As a result I correctly obtain in each of the
cells B1:B25 the corresponding cell in A1:A25
multiplied by 2

I tried to go further and defined a name to
represent the formula itself:
Name: formulaTimesTwo
Formula: =2*originalValues

Now I fill every cell of B1:B25 with
=formulaTimes2

This time the cells in B1:B25 all show,
incorrectly,the value of *A1* multiplied by 2. It is as if "originalValues" now refers uniquely
to cell A1 uniquely instead of cells A1:A25

Is this the way Excel works or am I doing something wrong? Thanks in advance for any help

 

[JE McGimpsey]

Is this the way Excel works or am I doing something wrong? Thanks in advance
for any help

It's the way XL works.

The formula

=2*originalvalues

returns an array of 25 values.

When normally entered in a cell in a row that intersects with the named
range, XL will display the result of the formula for that row. This
implied intersection is a useful shortcut.

However, entering the defined name returns the entire array, so each
cell will display the first result of that array.

One way to properly display the array is to select the result range,
then array-enter the defined name, using CMD-RETURN instead of RETURN.
When done correctly the formula bar will display brackets around the
defined name:

{=formulaTimes2}

 

[nunoferreira]

Of course, I should have known! Great. Thanks