Array Formula

  • Thread starter Thread starter Wayne Burrows
  • Start date Start date
W

Wayne Burrows

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
 
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 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 :-)))
 
Hi Frank,

Frank said:
Hi Alan
I wouldn't describe this as 'array of results'. This is a somehow
different type of object.
Click to expand...

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).
Click to expand...

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 :-)))
Click to expand...

Regards,
Alan Beban
 
Hi Alan
I don't know what you mean by "a different type of object".
Click to expand...

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'.

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?
Click to expand...

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,
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi Alan
I don't know what you mean by "a different type of object".
Click to expand...

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'.

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?
Click to expand...

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 !
--

Regards,

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,
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi Alan
I don't know what you mean by "a different type of object".
Click to expand...

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'.

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?
Click to expand...

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
 
RagDyeR said:
I've got to take some of that back.
With <Ctrl> <Enter>, *nothing* works correctly, not even in D1:D3 !
Click to expand...

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.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RagDyeR said:
I've got to take some of that back.
With <Ctrl> <Enter>, *nothing* works correctly, not even in D1:D3 !
Click to expand...

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.
 
Frank said:
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.
Click to expand...

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?

Alan Beban said:
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
Click to expand...
 
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
 
Back
Top