 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004Array Formula
I have this formula : =SUM(VLOOKUP(ABS(A2-A$2:A$4),Table,2)*SIGN(A2-A$2:A$4)) Which is entered as an array formula in cell B2. It is returning a value of 0 - this value is incorrect. Table has these values 0 0 20 1 50 2 a2-a4 have these values 110 140 150 The calculation that I want is : (110-110) lookup to give 0 (110-140) lookup to give -1 (110-150) lookup to give -1 So the total should be -2 What is going wrong here? If I enter the formula above in three contiguous cells and enter it as an array formula I get the correct result for cell b2 replicated. TIA Wayne Hi problem is that VLOOKUP does not return an array of results for this (test it in the formula bar with F9). The reason for this: Only MS knows :-) As an alternative formula (array entered): =SUM(LOOKUP(ABS(A2-$A$2:$A$4),D1:D3,SIGN(A2-$A$2:$A$4)*(E1:E3))) where D1:E3 is your table  Signature-- Regards Frank Kabel Frankfurt, Germany > I have this formula : > [quoted text clipped - 31 lines] > > Wayne Notwithstanding what the formula bar shows, VLOOKUP *does* return an array of results but Excel is a bit quirky on this one. If you array enter your formula into two or more cells (vertical or horizontal), it will return the desired result to each of the selected cells. Alan Beban > Hi > problem is that VLOOKUP does not return an array of results for this [quoted text clipped - 52 lines] >> >>Wayne Hi Alan I wouldn't describe this as 'array of results'. This is a somehow different type of object. And the result depends on the cell position you enter the formula in (so probably is using a kind of intersection operator). For my part I would NOT use VLOOKUP with the first parameter beeing an array as I wouldn't rely on the result of this operation. But this is just a personal comment :-))) Signature-- Regards Frank Kabel Frankfurt, Germany > Notwithstanding what the formula bar shows, VLOOKUP *does* return an > array of results but Excel is a bit quirky on this one. If you array [quoted text clipped - 59 lines] > >> > >>Wayne Hi Frank, > Hi Alan > I wouldn't describe this as 'array of results'. This is a somehow > different type of object. I don't know what you mean by "a different type of object". > And the result depends on the cell position > you enter the formula in (so probably is using a kind of intersection > operator). I guess I also don't know what you mean by "the cell position you enter the formula in"; could you give an example to show that you get different results depending on which two cells are used to array enter the result? > For my part I would NOT use VLOOKUP with the first parameter beeing an > array as I wouldn't rely on the result of this operation. But this is > just a personal comment :-))) Regards, Alan Beban Hi Alan > > Hi Alan > > I wouldn't describe this as 'array of results'. This is a somehow > > different type of object. > > I don't know what you mean by "a different type of object". the result of this function is neither an array of results (as you can see by pressing F9 in the formula bar and the non function of the initial formula the OP posted) nor a single result as it returns different values depending on the location of the target cell. I think Harlan once called this a 'different type of collection'. > > And the result depends on the cell position > > you enter the formula in (so probably is using a kind of intersection [quoted text clipped - 4 lines] > different results depending on which two cells are used to array enter > the result? lets assume the following setup: A B C 1 A 10 C 2 B 20 A 3 C 30 B Now try the following: 1. Select D1:D3 and enter the following formula with CTRL+ENTER (NOT as array formula) =VLOOKUP(C1:C3,A1:B3,2,0) And you'll get the (expected?) result of 30 10 20 2. Now enter the same in the cells E2:E4 and you'll get the result 10 20 #NA Personally I don't like the idea that the SAME formula I'm entering returns a DIFFERENT result depending on the cell location I'm entering this formula in. I know that the result would wlays be 30, 10, 20 if I would enter the formula as multi-cell array formula in 3 cells but still I find it strange that the non-array formula returns different results. And this is for me a good reason to avoid this kind of syntax. And coming back to 'this formula does not return an array'. If VLOOKUP would return an array the array formula =SUM(VLOOKUP(C1:C3,A1:B3,2,0)) would return 60. But it just returns 30 :-)) the same 'problem' can also be found for the INDEX function. On the other hand LOOKUP and MATCH DO return an array of scalars So I would consider this as one of Excel's mysteries. Maybe someone else could offer a more detailed explanation for this behaviour (Harlan?) Frank Frank, XPpro, XL'02, Your example works for me *accurately*, no matter where I enter it! Can't duplicate your "bad" results, SignatureRegards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- Hi Alan > > Hi Alan > > I wouldn't describe this as 'array of results'. This is a somehow > > different type of object. > > I don't know what you mean by "a different type of object". the result of this function is neither an array of results (as you can see by pressing F9 in the formula bar and the non function of the initial formula the OP posted) nor a single result as it returns different values depending on the location of the target cell. I think Harlan once called this a 'different type of collection'. > > And the result depends on the cell position > > you enter the formula in (so probably is using a kind of intersection > > operator). > > I guess I also don't know what you mean by "the cell position you enter > the formula in"; could you give an example to show that you get > different results depending on which two cells are used to array enter > the result? lets assume the following setup: A B C 1 A 10 C 2 B 20 A 3 C 30 B Now try the following: 1. Select D1:D3 and enter the following formula with CTRL+ENTER (NOT as array formula) =VLOOKUP(C1:C3,A1:B3,2,0) And you'll get the (expected?) result of 30 10 20 2. Now enter the same in the cells E2:E4 and you'll get the result 10 20 #NA Personally I don't like the idea that the SAME formula I'm entering returns a DIFFERENT result depending on the cell location I'm entering this formula in. I know that the result would wlays be 30, 10, 20 if I would enter the formula as multi-cell array formula in 3 cells but still I find it strange that the non-array formula returns different results. And this is for me a good reason to avoid this kind of syntax. And coming back to 'this formula does not return an array'. If VLOOKUP would return an array the array formula =SUM(VLOOKUP(C1:C3,A1:B3,2,0)) would return 60. But it just returns 30 :-)) the same 'problem' can also be found for the INDEX function. On the other hand LOOKUP and MATCH DO return an array of scalars So I would consider this as one of Excel's mysteries. Maybe someone else could offer a more detailed explanation for this behaviour (Harlan?) Frank I've got to take some of that back. With <Ctrl> <Enter>, *nothing* works correctly, not even in D1:D3 ! SignatureRegards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- Frank, XPpro, XL'02, Your example works for me *accurately*, no matter where I enter it! Can't duplicate your "bad" results, SignatureRegards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- Hi Alan > > Hi Alan > > I wouldn't describe this as 'array of results'. This is a somehow > > different type of object. > > I don't know what you mean by "a different type of object". the result of this function is neither an array of results (as you can see by pressing F9 in the formula bar and the non function of the initial formula the OP posted) nor a single result as it returns different values depending on the location of the target cell. I think Harlan once called this a 'different type of collection'. > > And the result depends on the cell position > > you enter the formula in (so probably is using a kind of intersection > > operator). > > I guess I also don't know what you mean by "the cell position you enter > the formula in"; could you give an example to show that you get > different results depending on which two cells are used to array enter > the result? lets assume the following setup: A B C 1 A 10 C 2 B 20 A 3 C 30 B Now try the following: 1. Select D1:D3 and enter the following formula with CTRL+ENTER (NOT as array formula) =VLOOKUP(C1:C3,A1:B3,2,0) And you'll get the (expected?) result of 30 10 20 2. Now enter the same in the cells E2:E4 and you'll get the result 10 20 #NA Personally I don't like the idea that the SAME formula I'm entering returns a DIFFERENT result depending on the cell location I'm entering this formula in. I know that the result would wlays be 30, 10, 20 if I would enter the formula as multi-cell array formula in 3 cells but still I find it strange that the non-array formula returns different results. And this is for me a good reason to avoid this kind of syntax. And coming back to 'this formula does not return an array'. If VLOOKUP would return an array the array formula =SUM(VLOOKUP(C1:C3,A1:B3,2,0)) would return 60. But it just returns 30 :-)) the same 'problem' can also be found for the INDEX function. On the other hand LOOKUP and MATCH DO return an array of scalars So I would consider this as one of Excel's mysteries. Maybe someone else could offer a more detailed explanation for this behaviour (Harlan?) Frank > I've got to take some of that back. > With <Ctrl> <Enter>, *nothing* works correctly, not even in D1:D3 ! I don't know what the <Ctrl> <Enter> syntax is normally expected to do, so I don't have any meaningful comment on it. My comments all related to array entering the formula. The difficulty I'm having is that if the formula is not returning an array of results, how is it that the the result of entering it into a two-cell range is the same as though it is returning the expected array of results? It's sort of a "looks like a duck, walks like a duck, quacks like a duck" problem. Alan Beban If you select a range C1:E50, and enter *anything* in the cell of focus, <Ctrl> <Enter> will simply fill the range with what was entered into the cell of focus, *including* formulas. In my *first* comment to Frank, I mistakenly used <Ctrl> <Shift> <Enter> to test his example, and found that the example worked correctly when array entered, no matter what cells the formula was entered into. Re-reading his post, I discovered my mistake in following his instructions. The use of <Ctrl> <Enter> does *not* prevent relative references from being altered. The use of CSE, however, *retains* even relative reference addresses throughout the entire range. SignatureRegards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- RagDyeR wrote: > I've got to take some of that back. > With <Ctrl> <Enter>, *nothing* works correctly, not even in D1:D3 ! I don't know what the <Ctrl> <Enter> syntax is normally expected to do, so I don't have any meaningful comment on it. My comments all related to array entering the formula. The difficulty I'm having is that if the formula is not returning an array of results, how is it that the the result of entering it into a two-cell range is the same as though it is returning the expected array of results? It's sort of a "looks like a duck, walks like a duck, quacks like a duck" problem. Alan Beban Hi Alan instead of using CTRL+ENTER you can achieve the same by simply entering each formula on its own in each cell. CTRL+ENTER just duplicates this in one step for the selected cells. For the other issue: As said this is something relly strane in Excel. It is definetly not documented in Excel's help. IMHO just a thing we have to live with. But there's definetly a difference to an array result as you can't sume the return values for example. Signature-- Regards Frank Kabel Frankfurt, Germany > > I've got to take some of that back. > > With <Ctrl> <Enter>, *nothing* works correctly, not even in D1:D3 ! [quoted text clipped - 10 lines] > > Alan Beban > Hi Alan . . . > > For the other issue: As said this is something relly strange in Excel. > It is definitely not documented in Excel's help. IMHO just a thing we > have to live with. But there's definitely a difference to an array > result as you can't sum the return values for example. Well, we're probably getting beyond the point of diminishing returns in this thread; but the crux of our difference--which may be only semantic--is that, more precisely, you *can indeed* sum (or index or match) the return values; you just can't do that *in a single cell*. Regards, Alan Beban What do you consider Transpose? > > Hi Alan . . . > > [quoted text clipped - 10 lines] > Regards, > Alan Beban I'm not sure what the question is, nor if it's even directed at me. Nevertheless, =TRANSPOSE(VLOOKUP(ABS(A2-A$2:A$4),D1:E3,2)*SIGN(A2-A$2:A$4)) for the ranges being discussed in this thread, returns values as though the VLOOKUP function were returning an array result. Alan Beban > What do you consider Transpose? > [quoted text clipped - 12 lines] >>Regards, >>Alan Beban |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.