"Here below is an inventory data table: [pic] We’ve known that the quantity of this product sold .."

mars RaqForum 25 No.
186 View • 1 Years ago

7.8 Early-terminated accumulation

desktop and excel data processing cases(180)

Here below is an inventory data table:

imagepng

We’ve known that the quantity of this product sold today is 50, and want to calculate the new inventory data (subtract the inventory quantity in turn according to the order in the table until 50 are subtracted in total, and keep only the rows with inventory quantity greater than 0):

	A
1	=E(‘A1:D18’)
2	=A1.iterate((a=min(Quantity,~~),Quantity-=a,~~-a),50,~~==0)
3	return A1.select(Quantity>0)

A2: Use iterate to loop the iteration, the ~~ in the loop represents the result of the last iteration, and its initial value is set to 50. Take the minimum value of the quantity of current row and ~~, and assign the value to the variable a; subtract a from the quantity of current row, and take ~~-a as the result of this iteration; When the iteration result ~~ is 0, terminate the iteration.
A3: Select the rows with quantity>0 in A1 after iteration.

imagepng