[Solved] auto less cn

[devroy]

in given data, there r to many customer transaction data, need help to do less all minus value automatic in fifo base , help me with formula

 

[shamimarita]

Hello Devroy,

To do FIFO allocation of minus values against plus values for each customer, you can use a helper column with a formula to track cumulative amounts.

• Sort your data by Customer and Date.
• Use a formula like this in your helper column (assuming amounts in F2):

=IF(F2>0,
MAX(0, F2 - SUMIFS(F$2:F2, A$2:A2, A2, F$2:F2, "<0")),
MIN(0, F2 + SUMIFS(F$2:F2, A$2:A2, A2, F$2:F2, ">0"))
)