Iterative subtraction

[nazuk]

Hello All,

Need your help for the following scenario

Shares	Net amount	Key	Difference
1000	456	A	2
2000	4566	A
3400	786800	A
24500	8700	B	3
2000	767800	B
5600	7869800	B
6700	7867800	B
7800	8700	B

So here i have to start subtracting the difference  Amount to all net amount such that it start assigning 0.01 from first net amount then the next group by key till whole difference is allocated to net amount.

I have already this in mind that i first calculate 

Difference/0.01*no of rows per key, and then start assigning that amount.

But how to do it that suppose there are 6 rows only and difference amount is say 0.02, then it should only allocate to first two net amount.

Can anyone help ?

[fmvizcaino]

Hi @nazuk ,

Would you be able to show the calculation for key A? It is not clear to me how this works.

Best,

Fernando V.

[nazuk]

Hi , so just take the below example

Shares	Net amount	Key	Difference	1st round	Net amount	2nd round	Net amount
1000	456	A	0.05	456+0.01	456.01	456.01+0.1	456.02
2000	4566	A	4566+0.01	4566.01	4566.01+0.1	4566.02
3400	786800	A	786800+0.01	786800.01

So here the difference amount is 0.05, so i have to start allocating this difference amount untill it is allocated to all. So i always have to start allocating to the small holding first 0.01 and later on, after one iteration we need to calculated how much is allocated and remaining need to do in next one

 output should be:

Shares	Net amount	Key	Difference
1000	456.02	A	0.05
2000	4566.02	A
3400	786800.01	A

[fmvizcaino · Accepted solution]

Hi @nazuk ,

I didn't follow your step by step  to create the logic because it would make the workflow more complex since we would need to create an iterative macro.. so I adapted the logic to make it easier.

Best,

Fernando Vizcaino