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

[Mark43]

Hi all,

Please see the attached screenshot. I want to calculate
the difference in column U cells when the column V value
changes from 1 to 0 and back again.

So in this example, W26 should show the difference
between U26 and U4 since rows 26 and 4 are the
rows where the V value changed from 1 to 0 and
from 0 to 1, respectively.

Thanks for your help!
Mark

[XOR LX]

Hi,

In W2:

=IF(AND(COUNTIF(V1,0),V2=1),U2-LOOKUP(1,0/V1:V$2,U2:U$3),"")

Copy down as required.

Regards

[Mark43]

Thanks! I'm going to have to study LOOKUP to make sense of that one.

[XOR LX]

You're welcome!

Regards

[Mark43]

Why the forward slash?

Thanks,
Mark

[XOR LX]

It represents division in Excel.

Regards

[Mark43]

I'm confused. For LOOKUP, isn't the first argument what to look for, the second argument a vector--[portion of] column or row to search to find the first argument--and the third argument another vector (corresponding [portion of] row or column to find the desired value? Zero divided by anything would be zero so why include the forward slash there?

[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

[Mark43]

Thanks XOR. I'm going to have to study that a bit.

Here's a follow-up question. I used your formula and then did a summation at the end of all the values in the column. I also did a COUNT to see how many times a number was calculated, which should have happened whenever a 0 changed to 1 going down the column.

Previously in another cell, I counted the number of "crossovers"--meaning a change from 0 to 1 or vice versa--using this formula:

=SUMPRODUCT(--(S21:S3906<>S22:S3907))

After completing the COUNT described above, I expected that number to roughly equal the SUMPRODUCT number because the latter is counting crossovers and the former has a difference calculated whenever a crossover occurs. I am seeing the SUMPRODUCT number to be twice the number of COUNT. Why?

[XOR LX]

But from your screenshot I assumed that the subtraction was only to be performed for cells where the column V entry has just switched from 0 to 1, and not for cells where it has switched from 1 to 0. So evidently the number of such calculations will be half the number of total "crossovers".

Regards

[Mark43]

Of course. Thanks again!

Mark

[Mark43]

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



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

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

[XOR LX]

So let's look at my attached spreadsheet

Can only see a screenshot, not an attachment?

Regards

[Mark43]

Sorry that's what I meant (screenshot).

Mark

[XOR LX]

Ah, then I'll have to pass, I'm afraid. Sometimes only an actual Excel file will do!

Hopefully someone else will pick up on this thread shortly. If not, you can always "bump" it after a certain amount of time has passed without you having received any responses.

Regards

[Mark43]

Sorry... I didn't realize it mattered. The spreadsheet
file is now attached.

I'll paste what I asked above:

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

[XOR LX]

Not sure I understand. That's working just as I would expect.

The last non-zero value in the first range, i.e. AH2:AH62, occurs in cell AH60.

Hence, since in the LOOKUP the result_vector is deliberately set to be equivalent to the lookup_vector offset by one row downwards, i.e. $B$3:$B3940 (this was one of the key points in my solution - strangely, you appear to have made the end row reference arbitrarily large; you're fortunate that this doesn't affect any results!), then the corresponding result from row 61 in column B is processed.

Regards

[Mark43]

Yes it works. I'm just trying to understand exactly how because I will have to use this again.
I think I finally get it.

My misunderstanding before was in thinking because the #DIV/0 were ignored the array got
SHORTENED. It does not.

And in looking for the last non-zero value in the array, that would be the last 1--and then
it looks to the next column shifted down one row. Perfect.

I do see, too, that I could have made the end row reference 62 instead of 3940. But why
am I fortunate this didn't affect results? The lookup_vector ends at AH62 so anything
below B63 would be ignored anyway, right?

Mark

Not sure I understand. That's working just as I would expect.

The last non-zero value in the first range, i.e. AH2:AH62, occurs in cell AH60.

Hence, since in the LOOKUP the result_vector is deliberately set to be equivalent to the lookup_vector offset by one row downwards, i.e. $B$3:$B3940 (this was one of the key points in my solution - strangely, you appear to have made the end row reference arbitrarily large; you're fortunate that this doesn't affect any results!), then the corresponding result from row 61 in column B is processed.

Regards

[XOR LX]

But why am I fortunate this didn't affect results?

Simply because a lot of functions aren't so lenient, in the sense that the arrays/ranges passed must be of an equal dimension.

Regards

[Mark43]

Got it.

Thanks much for your help and patience!

Mark

[TheN]

sorry wrong post

[Mark43]

I was going to say that I did go back and attach the spreadsheet.

But thanks... that makes sense. You can't work with a screenshot
or even copy/paste values/formulas into a real spreadsheet.

Mark

sorry wrong post