Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
I have a formula that includes the expression "min(L2,M2)". I want to be able
to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
=MIN(INDEX(colL,ROW()),INDEX(colM,ROW()))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Russell" wrote in message ... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
I don't understand - why doesn't
=MIN($L2:$M2) "seem to help (your) problem"? What happens when you drag that formula down the column? If you enter that in In article , Jim Russell wrote: I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
I'll assume your range names are "Col_L" and "Col_M".
Try using a range intersection....something like this: =MIN(Col_L 1:1,Col_M 1:1) That formula returns the minimum of these two values: The intersection of Col_L and row 1...which is L1 The intersection of Col_M and row 1...which is M1 Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
"Bob Phillips" wrote: =MIN(INDEX(colL,ROW()),INDEX(colM,ROW())) .... Thanks. That does indeed work, but it does do some violence to the readability of the formula. Interesting that "colM" (the range name by itself) returns the single value for the same row as the formula, but putting it in a MIN function does not work (e.g. MIN(colM,colL) -- that returns the min value of all cells in the named ranges. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
"JE McGimpsey" wrote: I don't understand - why doesn't =MIN($L2:$M2) "seem to help (your) problem"? What happens when you drag that formula down the column? That works fine (either with or without the $ absolute reference indicator). But I wanted to use a named range instead of L2 and M2. Bob Phillips suggeation to use index and row() to force a point index into the named ranges seems to do the trick, but I would rather it not be necessary. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
If you're referencing cells in the same row as the formula....
still using ranges named: Col_L and Col_M, try this: =MIN(+Col_L,+Col_M) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... I'll assume your range names are "Col_L" and "Col_M". Try using a range intersection....something like this: =MIN(Col_L 1:1,Col_M 1:1) That formula returns the minimum of these two values: The intersection of Col_L and row 1...which is L1 The intersection of Col_M and row 1...which is M1 Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
I like that. The best part is that the formula (in my case "MIN(SO25 2:2,SO50
2:2)" is relative, e.g. it does increment the row reference when it is copied down to subsequent rows. "Ron Coderre" wrote: I'll assume your range names are "Col_L" and "Col_M". Try using a range intersection....something like this: =MIN(Col_L 1:1,Col_M 1:1) That formula returns the minimum of these two values: The intersection of Col_L and row 1...which is L1 The intersection of Col_M and row 1...which is M1 Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
One possible alternative:
Select a cell in Row 1. Choose Insert/Name/Define: Names in workbook: LM Refers to: !$L1:$M1 Click OK Then use =MIN(LM) In article , Jim Russell wrote: That works fine (either with or without the $ absolute reference indicator). But I wanted to use a named range instead of L2 and M2. Bob Phillips suggeation to use index and row() to force a point index into the named ranges seems to do the trick, but I would rather it not be necessary. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
Yes, that works too!
Next question...Why? "Ron Coderre" wrote: If you're referencing cells in the same row as the formula.... still using ranges named: Col_L and Col_M, try this: =MIN(+Col_L,+Col_M) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... I'll assume your range names are "Col_L" and "Col_M". Try using a range intersection....something like this: =MIN(Col_L 1:1,Col_M 1:1) That formula returns the minimum of these two values: The intersection of Col_L and row 1...which is L1 The intersection of Col_M and row 1...which is M1 Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
Some functions (MIN, MAX, SUM, etc) are designed to work with ranges. Consequently, they default to reading the entire referenced
range. However, when an arithmetic operator is used on the range, Excel tries to find the intersection of the current row/col and the range. Example: A1: 10 A2: 20 A3: 30 B2: =SUM(A1:A3) returns 60 However, B2: =SUM(+A1:A3) returns 20 AND....B10: =SUM(+A1:A3) returns #VALUE! because there is NO interesection of Row_10 and A1:A3 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... Yes, that works too! Next question...Why? "Ron Coderre" wrote: If you're referencing cells in the same row as the formula.... still using ranges named: Col_L and Col_M, try this: =MIN(+Col_L,+Col_M) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... I'll assume your range names are "Col_L" and "Col_M". Try using a range intersection....something like this: =MIN(Col_L 1:1,Col_M 1:1) That formula returns the minimum of these two values: The intersection of Col_L and row 1...which is L1 The intersection of Col_M and row 1...which is M1 Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
If you use an array in a non-array function, it just returns the first
value. If you use it in an array function, such as MIN, the whole array is returned (and processed by that function). That's the way it works. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Russell" wrote in message ... "Bob Phillips" wrote: =MIN(INDEX(colL,ROW()),INDEX(colM,ROW())) ... Thanks. That does indeed work, but it does do some violence to the readability of the formula. Interesting that "colM" (the range name by itself) returns the single value for the same row as the formula, but putting it in a MIN function does not work (e.g. MIN(colM,colL) -- that returns the min value of all cells in the named ranges. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
Yes, thanks. From what little I rember of basic arithmetic (you know, before
the advanced topics like gozintas and take-aways) +x = x (at least for positive values of x). I'm glad that Microsoft decided to advance the state of the art! "Ron Coderre" wrote: Some functions (MIN, MAX, SUM, etc) are designed to work with ranges. Consequently, they default to reading the entire referenced range. However, when an arithmetic operator is used on the range, Excel tries to find the intersection of the current row/col and the range. Example: A1: 10 A2: 20 A3: 30 B2: =SUM(A1:A3) returns 60 However, B2: =SUM(+A1:A3) returns 20 AND....B10: =SUM(+A1:A3) returns #VALUE! because there is NO interesection of Row_10 and A1:A3 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... Yes, that works too! Next question...Why? "Ron Coderre" wrote: If you're referencing cells in the same row as the formula.... still using ranges named: Col_L and Col_M, try this: =MIN(+Col_L,+Col_M) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... I'll assume your range names are "Col_L" and "Col_M". Try using a range intersection....something like this: =MIN(Col_L 1:1,Col_M 1:1) That formula returns the minimum of these two values: The intersection of Col_L and row 1...which is L1 The intersection of Col_M and row 1...which is M1 Does that help? Post back if you have more questions. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Jim Russell" wrote in message ... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. (The formula should be min($L2,$M2) to keep the column references absolute, but that change does not seem to help my problem.) How should I name the columns to be able to apply them, and what should the formula look like when it is done? (I would guess the name would reference, e.g. $M$M, but what then? The rows do not have names.) Thanks! |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
Jim Russell wrote...
I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. . . . How should I name the columns to be able to apply them, and what should the formula look like when it is done? .... If this isn't just a contrived, oversimplified example, so the two columns in question really would be adjacent, the simplest, MOST READABLE formula would be =MIN($L2:$M2) If you really, really have to use defined names, then in any cell in the row in which you want the formula to reference L2:M2 define a name like ColsLM referring to =$L2:$M2. Then use the formula =MIN(ColsLM) If you fill this formula down into the next row, in that row ColsLM would refer to L3:M3 since ColLM is defined as column absolute but row relative, so the row portion of the range reference changes as formulas using the defined name are filled or copied into different rows. If you want two separate defined names, then in any cell in the row in which uou want the formula to reference L2 and M2 define the names ColL referring to =$L2 and ColM referring to =$M2. Then use the formula =MIN(ColL,ColM) |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Apply names to relative references
Excellent suggestion! Create a name referencing a single cell using column
absolute but row relative references. That works well and will get inserted by Insert/Name/Apply. This is one of those "Why didn't I think of that?" insights that help turn on the light bulb. In hindsight, I should have created the name to match the original cell reference in the initial formula and saved all my grief. Thanks very much to all who replied with such helpful insights. "Harlan Grove" wrote: Jim Russell wrote... I have a formula that includes the expression "min(L2,M2)". I want to be able to name columns L and M, and have those names used in the expression. The row number is relative, so the formulas below are "min(l3,M3)", etc. . . . How should I name the columns to be able to apply them, and what should the formula look like when it is done? .... If this isn't just a contrived, oversimplified example, so the two columns in question really would be adjacent, the simplest, MOST READABLE formula would be =MIN($L2:$M2) If you really, really have to use defined names, then in any cell in the row in which you want the formula to reference L2:M2 define a name like ColsLM referring to =$L2:$M2. Then use the formula =MIN(ColsLM) If you fill this formula down into the next row, in that row ColsLM would refer to L3:M3 since ColLM is defined as column absolute but row relative, so the row portion of the range reference changes as formulas using the defined name are filled or copied into different rows. If you want two separate defined names, then in any cell in the row in which uou want the formula to reference L2 and M2 define the names ColL referring to =$L2 and ColM referring to =$M2. Then use the formula =MIN(ColL,ColM) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative references | Excel Worksheet Functions | |||
Relative references | Excel Discussion (Misc queries) | |||
Relative references | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Selecting ALL names when using Insert/Names/Apply | Excel Worksheet Functions |