Name Assigned Formula Not Working

[FARAZ QURESHI]

I am using Version 2007. The following formula, when inserted manually on the
sheet, does workout but when defined in a name like "Check" and inserted as
=Check, in a cell, doesn't do so. Any particular reason and suggestion?
"VM" is also defined as $F$4.

=OR(AND(ISNUMBER(INDIRECT(ADDRESS(ROW(),COLUMN()-1))),OR(INDIRECT(ADDRESS(ROW(),COLUMN()-1))>=VM,INDIRECT(ADDRESS(ROW(),COLUMN()-1))<=-VM)),INDIRECT(ADDRESS(ROW(),COLUMN()-1))="Old",INDIRECT(ADDRESS(ROW(),COLUMN()-1))="New")

 

[Chip Pearson]

Without trying to figure out exactly what that formula is supposed to
do, it is worth noting that when a formula defined in a Name is
evaluated, it is always evaluated as if it were an array formula,
entered with CTRL SHIFT ENTER. (See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more
information about array formulas.)

For example, if A1:A3 have the values 11, 22, and 33, the function

=SUM(LARGE($A$1:$A$3,ROW($1:$2)))

evaluates to 33 if it is not array entered but evaluates to 55 if it
is array entered. If you put that formula in a defined name, the Name
evaluates to 55, not 33, illustrating how Names are evaluated as array
formulas.

Your formula when array entered returns a #VALUE error, so I think the
task at hand now is to re-construct the formula in a manner such that
it will return the correct result when evaluated as an array formula.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[FARAZ QURESHI]

Dear Chip,

Thanx for looking into the matter. What I require is to create a shortcut
instead of a lengthy formula, which shall:
1. Check the cell on the left;
2. Compare it with $F$4 (named "VM"); and
3. Return "TRUE" if:
(a) the cell on the left is greater than VM; or
(b) the cell on the left is less than -VM; or
(c) the cell on the left contains the text "old"; or
(d) the cell on the left contains the text "new";

You are quite right that when named, it returns a #Value error, but when
inserted manually as a Simple and not an Array, it works correctly.

I shall really appreciate if you could tell me how to overcome this
impediment and successfully name such a formula?

With Best Regards,
Your Fan,
FARAZ A. QURESHI

 

[FARAZ QURESHI]

Sorry I forgot one more condition in 3(a) & 3(b):

What I require is to create a shortcut instead of a lengthy formula, which
shall:

1. Check the cell on the left;
2. Compare it with $F$4 (named "VM"); and
3. Return "TRUE" if:
.. (a) the cell on the left is a number AND greater than VM; OR
.. (b) the cell on the left is a number AND less than -VM; OR
.. (c) the cell on the left contains the text "old"; OR
.. (d) the cell on the left contains the text "new";

You are quite right that when named, it returns a #Value error, but when
inserted manually as a SIMPLE and not an ARRAY, it works correctly.

By the way, how to insert an array formula in a defined name?

Thanx again,
Best Regards,
Your ever-admiring Fan

FARAZ A. QURESHI