Running percentages with changing denominators

Hi and welcome to the forum!

Would it be possible for you to attach a workbook with a few examples, and, importantly, your desired results clearly outlined in each case?

Regards

Thanks.

And just to be clear, those are your desired results in column D? And that you'd prefer to have one formula which can be copied down to give all those results, rather than having to manually amend it for varying SKUs?

Regards

Ok, then in D2 and copy down:

=C2/SUM(INDEX(C$2:C$1000,MATCH(A2,A$2:A$1000,0)):INDEX(C$2:C$1000,LOOKUP(2,1/(A$2:A$1000=A2),ROW(A$2:A$1000)-MIN(ROW(A$2:A$1000))+1)))

Regards

Sure.

We simply need to identify the range of summation in each case, whose starting and ending references will be the rows corresponding to the first and last occasions of the relevant SKU in column A respectively.

So, for example, taking your formula in cell E4, to obtain the equivalent of your manually-entered range of C2:C6 we need to identify rows 2 and 6: the first of these is done by this part of the formula:

INDEX(C$2:C$1000,MATCH(A4,A$2:A$1000,0))

which, since MATCH, when used with the appropriate match_type (zero here) gives the relative position of the first occurrence of the lookup_value within the specified range, will resolve to:

INDEX(C$2:C$1000,1)

In its normal use, INDEX returns the cell value specified by a given row and column. However, one of the nice things about this function is that, properly coerced (as here), it can also return an actual cell reference. Hence, in this construction, the above is actually resolving to $C$2, and not the actual value (5) in that cell.

The end range reference is identified by the second part:

INDEX(C$2:C$1000,LOOKUP(2,1/(A$2:A$1000=A4),ROW(A$2:A$1000)-MIN(ROW(A$2:A$1000))+1))

The trick with LOOKUP and reciprocation (the "1/" part) is a good one for finding the last occurrence of a value which meets certain criteria within a range. Since this function can operate on an array, this part:

(A$2:A$1000=A4)

returns a series of TRUE/FALSE responses to the test of which of those cell values is equal to the value in A4: in your example, the first few returns would be:

{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;,,,}

The clever bit is that, when properly coerced (by e.g. any mathematical operation), Boolean TRUE/FALSE values are converted into their numerical equivalent (TRUE=1, FALSE=0), so that, for example, TRUE*TRUE = 1*1 = 1, TRUE*FALSE = 1*0 = 0, FALSE*TRUE = 0*1 = 0 and FALSE*FALSE = 0*0 = 0, we also have:

1/TRUE = 1/1 = 1 and 1/FALSE = 1/0 = #DIV/0!

so 1/(A$2:A$1000=A4) will be (again, for the first few entries only):

1/{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;,,,}

which is:

{1;1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;,,,}

Since LOOKUP ignores errors and, if the lookup_value (2 here) is not found within the array, returns the value from the specified range (ROW(A$2:A$1000)-MIN(ROW(A$2:A$1000))+1, which I'll come to in a bit) whose relative position in that array corresponds to the last item in the array less than or equal to the lookup_value, in this case that value will be 5, the relative position of the last 1 in the array above.

This means that the LOOKUP will now take the 5th element in the array generated by:

ROW(A$2:A$1000)-MIN(ROW(A$2:A$1000))+1

which is a common construction to generate an array of consecutive integers from 1 onwards, i.e., and again, taking just the first few terms:

{1;2;3;4;5;6;7;8;9;10;11;12;...

(You can verify for yourself if you like how this is resolved.)

Hence, this part:

LOOKUP(2,1/(A$2:A$1000=A5),ROW(A$2:A$1000)-MIN(ROW(A$2:A$1000))+1)

has now finally resolved to 5 and so:

INDEX(C$2:C$1000,LOOKUP(2,1/(A$2:A$1000=A5),ROW(A$2:A$1000)-MIN(ROW(A$2:A$1000))+1))

will become simply $C$6 (as before), meaning that the whole formula now becomes:

C5/SUM($C$2:$C$6)

just as you had!

Regards

You're welcome.