DEV Community

Cover image for #83 — Early-Terminated Accumulation
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#83 — Early-Terminated Accumulation

Problem description & analysis:

Here below is an inventory data table:

source table

Task: We know 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).

Solution:

Use SPL XLL and enter the following code:

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

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 the current row and ~~, and assign the value to the variable a; subtract a from the quantity of the 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.

The results are shown below:

result table


Download esProc Desktop for FREE and subscribe for more data analysis tips!!! 🚀🔥⬇️

✨SPL download address: esProc Desktop FREE Download

✨Plugin Installation Method: SPL XLL Installation and Configuration

✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

✨YouTube FREE courses: SPL Programming

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.