Cumulative weights while satisfying a condition

[alanlambden]

Hi there,

I'm trying to run a small calculation that I can't seem to crack. I guess it would be best with a macro, but if it can be done using a formula, then that would be even better. Here's what I want to do:

Step one: Count down column B until cell value is no longer larger than the previous.
Step two: Sum all column A until column B is no longer larger than previous.
Step Three: Divide the cumulative sum of column A by total sum of column A until column B restarts at cell value of "1".

The answer I want is in Column C in the image below. I did this manually but I have many more records. In summary, calculate the cumulative weight of column A as a percentage of the total weight while column B counts upwards.

Apologies for the image, cant seem to install XL2BB. I'll keep trying.

 

[hagia_sofia]

I am not sure if you have 365 and if I understand it correctly that you are only interested in column C but test this:

=LET(
weight,A2:A19,
count,B2:B19,
group,SCAN(0,--(count=1),LAMBDA(a,b,a+b)),
sum,DROP(REDUCE("",group,LAMBDA(a,b,VSTACK(a,SUM(FILTER(weight,group=b))))),1),
proportion,weight/sum,
100*SCAN(0,proportion,LAMBDA(a,b,IF(a+b<=1,a+b,0+b))))

 

[alanlambden]

That seemed to work, thank you so much!

What tweaks would I make in order to calculate the proportion of values in the count column until the numbers reset? for example ... in column B working down the column until the numbers resest to 1 ... 1 would be 20% of 5, 2 is 40% of 5 and so on until it resets and then 1 is 16.6% of 6 and so on.

 

[alanlambden]

Here is a pic to help demonstrate:

I want to find the value in %cumcount which I did manually. Using the highest number in the sequence until it starts again when resetting to 1.

 

[hagia_sofia]

Please test this:

=LET(
weight,A2:A19,
count,B2:B19,
filter,FILTER(weight,count=1),
seq_1,SEQUENCE(ROWS(weight),,1,0),
seq_2,DROP(REDUCE("",filter,LAMBDA(a,b,VSTACK(a,SEQUENCE(b,,b,0)))),1),
proportion,seq_1/seq_2,
SCAN(0,proportion,LAMBDA(a,b,IF(a+b<=1,a+b,0+b))))

 

[Cubist]

Try

Book3
	A	B	C
1
2	1		0.20
3	2		0.40
4	3		0.60
5	4		0.80
6	5		1.00
7	1		0.17
8	2		0.33
9	3		0.50
10	4		0.67
11	5		0.83
12	6		1.00
13	1		0.14
14	2		0.29
15	3		0.43
16	4		0.57
17	5		0.71
18	6		0.86
19	7		1.00
Sheet2

Cell Formulas
Range	Formula
C2:C19	C2	=A2/INDEX(VSTACK(TOCOL(IFS($A$3:$A$19=1,$A$2:$A$18),2),$A$19),COUNTIFS($A$2:A2,1))

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)