Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name Assigned Formula Not Working
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") -- Best Regards, FARAZ A. QURESHI |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name Assigned Formula Not Working
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) On Tue, 30 Dec 2008 00:01:04 -0800, FARAZ QURESHI wrote: =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") |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name Assigned Formula Not Working
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 "Chip Pearson" wrote: 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) On Tue, 30 Dec 2008 00:01:04 -0800, FARAZ QURESHI wrote: =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") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name Assigned Formula Not Working
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 "Chip Pearson" wrote: 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) On Tue, 30 Dec 2008 00:01:04 -0800, FARAZ QURESHI wrote: =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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to color fill a cell if number over assigned value | Excel Worksheet Functions | |||
assigned cell names | Excel Discussion (Misc queries) | |||
Macro Assigned to a Button | Excel Discussion (Misc queries) | |||
fourmla with result no less than assigned value | Excel Worksheet Functions |