Calcuate difference in one column based on value change in another column

Okay going back to your explanation here, I understand
how anything that isn't a natural number gets reduced
to #DIV/0 (error) and therefore left out of the array.
It will therefore use the row corresponding to the
number of 1's (my columns are either 0 or 1).

So let's look at my attached spreadsheet that was
calculated correctly in AI63 as

=IF(AND(COUNTIF(AH62,0),AH63=1),LOOKUP(1,0/AH$2:AH62,$B$3:$B3940)-$B63,"")

So let's see... it should have done B61-B63 (it did).

But how did it get B61? AH2:AH56 are all blank so
those are ignored, right? AH57:AH58 are 0s, which
become errors upon reciprocation so those are
ignored. AH59:AH60 are both 1s and then you have
the 0 in AH61. So if it is looking at the array
from AH2:AH62 then wouldn't that reduce to just
two cells: AH2 and AH3? Then it if took the last
one, AH3, wouldn't it look at the corresponding
second piece of data which would be B4 (since
it starts at B3)? B4 would be wrong.

Thanks,
Mark



---------------------------------------------------------------------------------------------

Originally Posted by XOR LX

Using the data you posted and taking the formula in cell W26, this part:

LOOKUP(1,0/V$2:V25,U$3:U26)

which, putting in the entries from V2:V25, is:

LOOKUP(1,0/{1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},U$3:U26)

and, after reciprocation with zero:

LOOKUP(1,{0;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},U$3:U26)

The point being that, any zeroes within that range have now been rendered as errors. As such, they will be ignored by LOOKUP (one of its useful properties), which was precisely the reason for the prior reciprocation.

And, providing the lookup_vector is sorted (which, since it contains nothing but zeroes (recall that we ignore the errors), can certainly be said for that array in this instance), then, if the lookup_value is greater than all values within the lookup_vector (the choice of 1 here is therefore always sufficient, though any other value >0 would also suffice), the entry corresponding to the last numerical value within the range is returned. Here that is equivalent to the last zero within that array, highlighted in the above.

Regards

Sorry that's what I meant (screenshot).

Mark