• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sequence Number Required

Naresh Yadav

Active Member
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

QtyDesire Sequence Number|
Formula Used
Remarks
2 =CEILING(SUM(A$2:A2),20)/20OK
11OK
31OK
51OK
21OK
51OK
92OK
22OK
52OK
42OK
22Wrong Result|Sum of the Qty in Sequence Number have Increased from 20
13OK
33OK
13OK
13OK
43OK
23OK
23OK
23OK
54OK
14OK
54OK
54OK
44OK
24Wrong Result|Sum of the Qty in Sequence Number have Increased from 20
45OK
25OK
35OK

regards
Naresh
 

Attachments

Naresh Yadav

Active Member
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..
 

p45cal

Well-Known Member
Without a helper column:
In B2 enter the number 1
In B3:
Code:
=IF((SUMIF(B$2:B2,B2, A$2:A2)+A3)>20,MAX(B$2:B2)+1,B2)
copied down.
 
Top