Question: I have two volumes which keeps on varying in L1 & L2 head. This is the only info i get everyday.

Now i just want under each head i.e. L1 & L2 the respective volume whether it's 4000 or any other figure say 4311 etc gets automatically divided into batches of 250 each as shown in Column A and col. D for L1 & L2 heads respectively.

If the bal volume left which is not equal to 250 then it shld appear in last row under respective head e.g. 120 in col A.

Other requirement is those batches get numbered automatically but the numbering should remain continuous when it goes from L1 to L2 head. it should not commence from 1 for L2 head. The numbering shld be done for 250 volume batches and in the last it should number for the batches not equal to 250 volume.

Answer:

Array formula in A3:

=IF(SUM(A2:$A$2)=$I$3,"",IF(ROWS(A3:$A$3)<=INT($I$3/250),250,$I$3-(ROWS(A3:$A$3)-1)*250)) + CTRL + SHIFT + ENTER

copied down as far as needed.

Array formula in B3:

=IF(A3="","",IF(A3=250,ROWS(B3:$B$3),ROUNDUP(($I$3+$I$4)/250,0))) + CTRL + SHIFT + ENTER

copied down as far as needed.

Array formula in D3:

=IF(SUM($D2:D$2)=$I$4,"",IF(ROWS($D3:D$3)<=INT($I$4/250),250,$I$4-(ROWS($D3:D$3)-1)*250))  + CTRL + SHIFT + ENTER

 copied down as far as needed.

Array formula in E3:

=IF(D3="","",IF(D3=250,ROWS($E3:E$3)+INT($I$3/250),IF($I$3/250-INT($I$3/250)=0,ROUNDUP(($I$3+$I$4)/250,0),ROUNDUP(($I$3+$I$4)/250,0)+1))) + CTRL + SHIFT + ENTER

copied down as far as needed.

Download excel example file

Sequencing and numbering of batches.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

ROWS(array) returns the number of rows in a reference or an array

INT(number)
Rounds a number down to nearest integer

Roundup(number)
Rounds a number up, away from zero.