This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
hey @Ekta Would love to help but I don't think I understand exactly the calculation required. What do you mean by 'offset hte -ve value from the first positive value for each ID'. Unless I'm mistaken, should the first row of output be:
Given the input:
my thinking is the first positive number (10), take away the negative value (-5), would be '5', not '-5'.
I think you missed a few things out here, as in you don't simply off-set against the first positive value, but you off-set the amount against the first value, then the remainder against the next value, and the remainder against the next value etc. until the there is no negative value left. Once the amount has been subsumed by the negative value then it needs to be removed.
And I assume you got the first value wrong, and you meant 5 not -5, otherwise none of it makes sense.
Anyway, I've attached the workflow. Basically I used a sum and join to associate the negative value back to the appropriate IDs, then a multi-row formula to assign the negative balance.