Sequence Number Required

[Naresh Yadav]

Respected All,

I need a Formula that can allow me to generate the sequence number against the Qty if sum of the qty <=20
i had tried using =CEILING(SUM(A$2:A2),20)/20 formula but couldn't reach to the required result
please help and guide me for the solution

Qty	Desire Sequence Number| Formula Used	Remarks
2	=CEILING(SUM(A$2:A2),20)/20	OK
1	1	OK
3	1	OK
5	1	OK
2	1	OK
5	1	OK
9	2	OK
2	2	OK
5	2	OK
4	2	OK
2	2	Wrong Result|Sum of the Qty in Sequence Number have Increased from 20
1	3	OK
3	3	OK
1	3	OK
1	3	OK
4	3	OK
2	3	OK
2	3	OK
2	3	OK
5	4	OK
1	4	OK
5	4	OK
5	4	OK
4	4	OK
2	4	Wrong Result|Sum of the Qty in Sequence Number have Increased from 20
4	5	OK
2	5	OK
3	5	OK

regards
Naresh

 

[Juniad]

Is this formula meets your requirements?
=IF(SUM($A$2:A2)<=20,SUBTOTAL(3,$A$2:A2),"")

 

[Naresh Yadav]

Dear Juniad,

thanks for your effort, but couldn't reach to the required result
i want the formula that generate the same sequence number until the sum value <=20
and then next sequence number should change and remain same till sum value reach <=20 and so on..

 

[Juniad]

Check the expected result with helper column.

 

[p45cal]

Without a helper column:
In B2 enter the number 1
In B3:

=IF((SUMIF(B$2:B2,B2, A$2:A2)+A3)>20,MAX(B$2:B2)+1,B2)

copied down.