Array Formula Query continued...

[2016LM]

Hi,


I posted an array formula question on the 2nd of Nov at 09:17, which was answered, but frustrating only raised more questions. Having done a bit more reading-up on array formulae, I thought I would try and answer them myself. The problem I have is with the following formula:

SUM(OFFSET($A$1,MATCH($A$5:$A$9,$A$1:$A$3,0)-1,1,1,1))

In cells A1:B3 I have a look-up table and A5:A9 a column of values. The intent of the formula is to step thru the values in A5:A9, look-up the value in A1:A3, note the value in the adjacent column B1:B3, then add these valves (the total is 48). The values quoted in the noted ranges are as follows:

A1:A3 = {1;2;3}, B1:B3 = {7;10;12} and A5:A9 ={1;1;3;3;2}


When I input: SUM(OFFSET($A$1,MATCH($A$5:$A$9,$A$1:$A$3,0)-1,1,1,1)) and press Ctrl + Shift + Enter the cell returns 7 (and not 48).


However, when I highlight MATCH($A$5:$A$9,$A$1:$A$3,0)-1 and press F9, it is replaced with {0;0;2;2;1} i.e. SUM(OFFSET($A$1,{0;0;2;2;1},1,1,1)). Then if I highlight the OFFSET function and press F9 the formula simplifies to SUM({7;7;12;12;10}), which after pressing Ctrl + Shift + Enter returns 48 i.e. the answer.

My question is, why when I step through the formula using F9 do I get the desired result, yet when I input SUM(OFFSET($A$1,MATCH($A$5:$A$9,$A$1:$A$3,0)-1,1,1,1)) the output is 7 (i.e. not the answer, least not the answer I want).

Any help / assistance would be massively appreciated.

Kind regards,

2016LM

 

[XOR LX]

Hi,

Evaluating a formula via piecemeal application of F9 to parts of that formula is not to be recommended, since that method will always 'evaluate' to an array of returns where possible, even if the necessary coercion is lacking in reality. Using the Evaluate Formula tool avoids this issue.

OFFSET often requires a bit of coercion. In this case, N will suffice:

=SUM(N(OFFSET($A$1,MATCH($A$5:$A$9,$A$1:$A$3,0)-1,1,1,1)))

Regards

 

[Aladin Akyurek]

What F9, which is quite adequate for the job, shows in this case is that SUM fails to access the evaluation. This is known as the dereferencing problem and it requires a second round of evaluation to get access to the evaluation. One way to obtain a second round of evaluation is to submit the OFFSET evaluation to the N function:


=SUM(N(OFFSET($A$1,MATCH($A$5:$A$9,$A$1:$A$3,0)-1,1,1,1)))

Another way is using SUBTOTAL to the same end:

=SUM(SUBTOTAL(9,OFFSET($A$1,MATCH($A$5:$A$9,$A$1:$A$3,0)-1,1,1,1)))

Both formulas require applying control+shift+enter, not just enter.

 

[2016LM]

Thanks for the prompt replies XOR LX and AladinAkyurek, and apologies for my tardy reply / thanks.


I was not aware of the Evaluate Formula tool (thanks for the advice), but have now been usingit to step through the formula. Inoticed that it evaluates to SUM(N({#VALUE !; #VALUE !; #VALUE !; #VALUE !; #VALUE !})),which then evaluates to SUM({7;7;12;12;10}). I’m assuming this is the dereferencing problem that’s resolved / coercedby the N function.

Thanks again for your help XOR LX and AladinAkyurek

Kind regards,
2016LM

 

[Aladin Akyurek]

Thanks for the prompt replies XOR LX and AladinAkyurek, and apologies for my tardy reply / thanks.


I was not aware of the Evaluate Formula tool (thanks for the advice), but have now been usingit to step through the formula. Inoticed that it evaluates to SUM(N({#VALUE !; #VALUE !; #VALUE !; #VALUE !; #VALUE !})),which then evaluates to SUM({7;7;12;12;10}). I’m assuming this is the dereferencing problem that’s resolved / coercedby the N function.

Thanks again for your help XOR LX and AladinAkyurek

Kind regards,
2016LM

It's as we call it the dereferencing problem and a second round of evaluation (with N or SUBTOTAL here) is needed to be resolved. Try to Google on "dereferencing" (also its mistyped version: "deferencing") and/or "second round of evaluation"). Once again, F9 is a solid technique for investigating parts of a formula (Try always on small definte ranges.).

 

[2016LM]

Will do. Thanks Aladin Akyurek.

Kind regards,
2016LM

 

[Aladin Akyurek]

Will do. Thanks Aladin Akyurek.

Kind regards,
2016LM

You are welcome.