# Convert complex Excel function into KNIME **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [June 5, 2024, 2:33am UTC](https://forum.knime.com/t/convert-complex-excel-function-into-knime/80075 "2024-06-05T02:33:29Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Nguyen\_Huynh\_Du](https://forum-cdn.knime.com/user_avatar/forum.knime.com/nguyen_huynh_du/32/68844_2.png) [@Nguyen\_Huynh\_Du](https://forum.knime.com/u/Nguyen_Huynh_Du)\ **Post date:** [June 5, 2024, 2:33am UTC](https://forum.knime.com/t/convert-complex-excel-function-into-knime/80075/1 "2024-06-05T02:33:29Z")
Hi everyone, I have built this excel function and it I don’t know how to convert it to KNIME. Here is my data, and the column Refund is my desired result also the result of this formula, starting from row 2: =IF(C2-SUM(FILTER(F:F,A:A=A2,E:E\>=E2))\>=0,F2,IF(C2-SUM(FILTER(F:F,A:A=A2,E:E\>=E2))+F2\>=0,C2-SUM(FILTER(F:F,A:A=A2,E:E\>=E2))+F2,0)) | user\_name | Code | amount | Ref number | Date | Total | Refund | | --- | --- | --- | --- | --- | --- | --- | | Vietnam | 372449446 | 10,000,000 | 2551 | 2024-05-02 | 10,000,000 | 10,000,000 | | Vietnam | 372449446 | 10,000,000 | 105373 | 2024-03-03 | 200,000 | 0 | | United States | 1109683890 | 10,000,000 | 2725 | 2024-05-02 | 70,000,000 | 10,000,000 | | United Kingdom | 541673807 | 10,000,000 | 2619 | 2024-05-02 | 10,000,000 | 10,000,000 | | United Arab Emirates | 904448505 | 10,000,000 | 3170 | 2024-05-02 | 10,000,000 | 10,000,000 | | Turkey | 603732558 | 12,000,000 | 2830 | 2024-05-02 | 10,000,000 | 10,000,000 | | Turkey | 603732558 | 12,000,000 | 68901 | 2024-02-04 | 200,000 | 200,000 | | Thailand | 1036127587 | 10,000,000 | 3157 | 2024-05-02 | 10,000,000 | 10,000,000 | | Spain | 1008863264 | 10,300,000 | 2673 | 2024-05-02 | 10,000,000 | 10,000,000 | | Spain | 1008863264 | 10,300,000 | 141975 | 2024-04-02 | 600,000 | 300,000 | | South Africa | 645067822 | 10,000,000 | 3377 | 2024-05-02 | 10,000,000 | 10,000,000 | | Singapore | 903750383 | 9,800,000 | 2743 | 2024-05-02 | 10,000,000 | 9,800,000 | | Saudi Arabia | 549777848 | 9,600,000 | 2790 | 2024-05-02 | 10,000,000 | 9,600,000 | | Russia | 1041407098 | 10,000,000 | 2744 | 2024-05-02 | 10,200,000 | 10,000,000 | | Russia | 1041407098 | 10,000,000 | 68704 | 2024-02-04 | 1,600,000 | 0 | | Philippines | 829374517 | 10,000,000 | 2900 | 2024-05-02 | 10,000,000 | 10,000,000 | | Peru | 44808261 | 10,000,000 | 2701 | 2024-05-02 | 10,000,000 | 10,000,000 | | Pakistan | 311832019 | 10,000,000 | 2896 | 2024-05-02 | 10,000,000 | 10,000,000 | | Nigeria | 256445542 | 20,000,000 | 2336 | 2024-05-02 | 20,000,000 | 20,000,000 | | Nigeria | 256445542 | 20,000,000 | 105040 | 2024-03-03 | 200,000 | 0 | | Mexico | 613539683 | 10,000,000 | 3222 | 2024-05-02 | 10,000,000 | 10,000,000 | | Malaysia | 670405651 | 10,000,000 | 3136 | 2024-05-02 | 10,000,000 | 10,000,000 | | Laos | 962880062 | 10,000,000 | 3000 | 2024-05-02 | 10,000,000 | 10,000,000 | | Korea | 1043947886 | 20,000,000 | 2669 | 2024-05-02 | 20,000,000 | 20,000,000 | | Korea | 1043947886 | 20,000,000 | 141962 | 2024-04-02 | 600,000 | 0 | | Kenya | 1014413172 | 200,000 | 2952 | 2024-05-02 | 200,000 | 200,000 | | Japan | 293754452 | 10,000,000 | 3253 | 2024-05-02 | 20,000,000 | 10,000,000 | | Japan | 293754452 | 10,000,000 | 68922 | 2024-02-04 | 400,000 | 0 | | Italy | 1158583813 | 10,000,000 | 3218 | 2024-05-02 | 10,000,000 | 10,000,000 | | Indonesia | 358057353 | 20,000,000 | 2993 | 2024-05-02 | 20,000,000 | 20,000,000 | | India | 53831405 | 10,000,000 | 2836 | 2024-05-02 | 10,600,000 | 10,000,000 | | Germany | 226630716 | 10,000,000 | 2421 | 2024-05-02 | 10,000,000 | 10,000,000 | | France | 58239472 | 10,000,000 | 3201 | 2024-05-02 | 10,000,000 | 10,000,000 | | Egypt | 598833157 | 26,000,000 | 2342 | 2024-05-02 | 20,000,000 | 20,000,000 | | Egypt | 598833157 | 26,000,000 | 141676 | 2024-04-02 | 7,200,000 | 6,000,000 | | Egypt | 598833157 | 26,000,000 | 105046 | 2024-03-03 | 200,000 | 0 | | Colombia | 158649922 | 300,000 | 105296 | 2024-03-03 | 200,000 | 200,000 | | Colombia | 158649922 | 300,000 | 68849 | 2024-02-04 | 200,000 | 100,000 | | China | 15276633 | 200,000 | 3289 | 2024-05-02 | 200,000 | 200,000 | | Chile | 151470 | 10,000,000 | 3372 | 2024-05-02 | 10,000,000 | 10,000,000 | | Canada | 897852173 | 10,000,000 | 3037 | 2024-05-02 | 20,000,000 | 10,000,000 | | Cambodia | 600526694 | 10,000,000 | 3306 | 2024-05-02 | 10,000,000 | 10,000,000 | | Brazil | 466493113 | 10,000,000 | 2899 | 2024-05-02 | 10,000,000 | 10,000,000 | | Bangladesh | 1155821483 | 10,000,000 | 2809 | 2024-05-02 | 10,000,000 | 10,000,000 | | Australia | 266622764 | 10,000,000 | 2950 | 2024-05-02 | 10,000,000 | 10,000,000 | | Argentina | 55053875 | 10,000,000 | 2606 | 2024-05-02 | 10,000,000 | 10,000,000 | If you see this content among my posts, it’s correct. Because I am new to KNIME, I try to learn all the stuff that KNIME can do haha. So if you don’t mind, please supporting me on my learning KNIME journey. Thank you everyone. Hope to see your idea. ---
**Author:** ![yogesh\_nawale](https://forum-cdn.knime.com/user_avatar/forum.knime.com/yogesh_nawale/32/73209_2.png) [@yogesh\_nawale](https://forum.knime.com/u/yogesh_nawale)\ **Post date:** [June 5, 2024, 6:19am UTC](https://forum.knime.com/t/convert-complex-excel-function-into-knime/80075/2 "2024-06-05T06:19:29Z")
Steps 1. Excel Reader 2. String to Date/Time node - if needed to convert sting column to Date&Time data type 3. Group Loop Start - (group by ‘Code’) 4. Sorter Node (sort by ‘Date’) 5. Moving Aggregation- (cumulative sum of ‘Total’) 6.Java Snippet - (calculate refund) 6. Loop End 7. Joiner Node (if needed) use this in Java Snippet Node double refund; double remainingAmount = c\_amount - c\_MATotal; if (remainingAmount \>= 0) { refund = c\_Total; } else if (remainingAmount + c\_Total \>= 0) { refund = c\_amount - c\_MATotal + c\_Total; } else { refund = 0; } out\_Refund = refund; Below is attached workflow…Make changes as per your requirment [QnA2.knwf](https://forum.knime.com/uploads/short-url/mHxB1nY312Mov6DUejBoDU7ZVhE.knwf) (83.3 KB) ---
**Author:** ![Nguyen\_Huynh\_Du](https://forum-cdn.knime.com/user_avatar/forum.knime.com/nguyen_huynh_du/32/68844_2.png) [@Nguyen\_Huynh\_Du](https://forum.knime.com/u/Nguyen_Huynh_Du)\ **Post date:** [June 5, 2024, 6:39am UTC](https://forum.knime.com/t/convert-complex-excel-function-into-knime/80075/3 "2024-06-05T06:39:27Z")
Hi @yogesh_nawale Thanks for reply me. However, when I downloaded your workflow and ran it, the result was not as I showed. _ **See case Egypt.** _ Can you check it again and let me know what should I check or change please. | user\_name | Code | amount | Ref number | Date | Total | Refund | | --- | --- | --- | --- | --- | --- | --- | | Spain | 1008863264 | 10000000 | 2673 | 2024-05-02 | 10000000 | 10000000 | | Spain | 1008863264 | 10000000 | 141975 | 2024-04-02 | 600000 | 600000 | | Kenya | 1014413172 | 200000 | 2952 | 2024-05-02 | 200000 | 200000 | | Thailand | 1036127587 | 10000000 | 3157 | 2024-05-02 | 10000000 | 10000000 | | Russia | 1041407098 | 10000000 | 2744 | 2024-05-02 | 10200000 | 10000000 | | Russia | 1041407098 | 10000000 | 68704 | 2024-02-04 | 1600000 | 1600000 | | Korea | 1043947886 | 20000000 | 2669 | 2024-05-02 | 20000000 | 20000000 | | Korea | 1043947886 | 20000000 | 141962 | 2024-04-02 | 600000 | 600000 | | United States | 1109683890 | 10000000 | 2725 | 2024-05-02 | 70000000 | 10000000 | | Bangladesh | 1155821483 | 10000000 | 2809 | 2024-05-02 | 10000000 | 10000000 | | Italy | 1158583813 | 10000000 | 3218 | 2024-05-02 | 10000000 | 10000000 | | Chile | 151470 | 10000000 | 3372 | 2024-05-02 | 10000000 | 10000000 | | China | 15276633 | 200000 | 3289 | 2024-05-02 | 200000 | 200000 | | Colombia | 158649922 | 200000 | 68849 | 2024-02-04 | 200000 | 200000 | | Colombia | 158649922 | 200000 | 105296 | 2024-03-03 | 200000 | 200000 | | Germany | 226630716 | 10000000 | 2421 | 2024-05-02 | 10000000 | 10000000 | | Nigeria | 256445542 | 20000000 | 2336 | 2024-05-02 | 20000000 | 20000000 | | Nigeria | 256445542 | 20000000 | 105040 | 2024-03-03 | 200000 | 200000 | | Australia | 266622764 | 10000000 | 2950 | 2024-05-02 | 10000000 | 10000000 | | Japan | 293754452 | 10000000 | 3253 | 2024-05-02 | 20000000 | 10000000 | | Japan | 293754452 | 10000000 | 68922 | 2024-02-04 | 400000 | 400000 | | Pakistan | 311832019 | 10000000 | 2896 | 2024-05-02 | 10000000 | 10000000 | | Indonesia | 358057353 | 20000000 | 2993 | 2024-05-02 | 20000000 | 20000000 | | Vietnam | 372449446 | 10000000 | 2551 | 2024-05-02 | 10000000 | 10000000 | | Vietnam | 372449446 | 10000000 | 105373 | 2024-03-03 | 200000 | 200000 | | Peru | 44808261 | 10000000 | 2701 | 2024-05-02 | 10000000 | 10000000 | | Brazil | 466493113 | 10000000 | 2899 | 2024-05-02 | 10000000 | 10000000 | | India | 53831405 | 10000000 | 2836 | 2024-05-02 | 10600000 | 10000000 | | United Kingdom | 541673807 | 10000000 | 2619 | 2024-05-02 | 10000000 | 10000000 | | Saudi Arabia | 549777848 | 9600000 | 2790 | 2024-05-02 | 10000000 | 9600000 | | Argentina | 55053875 | 10000000 | 2606 | 2024-05-02 | 10000000 | 10000000 | | France | 58239472 | 10000000 | 3201 | 2024-05-02 | 10000000 | 10000000 | | Egypt | 598833157 | 26000000 | 2342 | 2024-05-02 | 20000000 | 20000000 | | Egypt | 598833157 | 26000000 | 105046 | 2024-03-03 | 30000000 | 26000000 | | Egypt | 598833157 | 26000000 | 141676 | 2024-04-02 | 7200000 | 7200000 | | Cambodia | 600526694 | 10000000 | 3306 | 2024-05-02 | 10000000 | 10000000 | | Turkey | 603732558 | 10000000 | 2830 | 2024-05-02 | 10000000 | 10000000 | | Turkey | 603732558 | 10000000 | 68901 | 2024-02-04 | 200000 | 200000 | | Mexico | 613539683 | 10000000 | 3222 | 2024-05-02 | 10000000 | 10000000 | | South Africa | 645067822 | 10000000 | 3377 | 2024-05-02 | 10000000 | 10000000 | | Malaysia | 670405651 | 10000000 | 3136 | 2024-05-02 | 10000000 | 10000000 | | Philippines | 829374517 | 10000000 | 2900 | 2024-05-02 | 10000000 | 10000000 | | Canada | 897852173 | 10000000 | 3037 | 2024-05-02 | 20000000 | 10000000 | | Singapore | 903750383 | 9800000 | 2743 | 2024-05-02 | 10000000 | 9800000 | | United Arab Emirates | 904448505 | 10000000 | 3170 | 2024-05-02 | 10000000 | 10000000 | | Laos | 962880062 | 10000000 | 3000 | 2024-05-02 | 10000000 | 10000000 | ---
**Author:** ![yogesh\_nawale](https://forum-cdn.knime.com/user_avatar/forum.knime.com/yogesh_nawale/32/73209_2.png) [@yogesh\_nawale](https://forum.knime.com/u/yogesh_nawale)\ **Post date:** [June 5, 2024, 10:16am UTC](https://forum.knime.com/t/convert-complex-excel-function-into-knime/80075/4 "2024-06-05T10:16:10Z")
Please check the moving aggregation node configuration Find proper way to find the cumulative Total ---
**Author:** ![Nguyen\_Huynh\_Du](https://forum-cdn.knime.com/user_avatar/forum.knime.com/nguyen_huynh_du/32/68844_2.png) [@Nguyen\_Huynh\_Du](https://forum.knime.com/u/Nguyen_Huynh_Du)\ **Post date:** [June 5, 2024, 11:22am UTC](https://forum.knime.com/t/convert-complex-excel-function-into-knime/80075/5 "2024-06-05T11:22:45Z")
Hi @yogesh_nawale The MA is correct, the Java Snippet is correct. But that’s not enough. The thing is, all of them have to be based on **user\_name** column. Do you have any idea for that? ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [September 3, 2024, 11:23am UTC](https://forum.knime.com/t/convert-complex-excel-function-into-knime/80075/6 "2024-09-03T11:23:20Z")
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.