A method of calculating values under special conditions

[phoebe_90]

When Document Number and Tax Code is the same, this type of row is greater than 2, How do I make the value of the last row equal to the value of the corresponding "Amount in local currency" minus the value of the "Amount" column in the previous rows?

Amount=Amount in local currency*percentage

For example, change sheet1 to sheet2.

sheet1

Document Number	Tax Code	Account	Input_#2_Account	Amount in local currency	percentage	Amount
19000498	J2	24508210	66020000	0.45	1	0.45
19000477	J1	24508210	65040000	376.42	1	376.42
19000477	J2	24508210	65010000	1061.44	0.33	350.28
19000477	J2	24508210	65010000	1061.44	0.33	350.28
19000477	J2	24508210	65010000	1061.44	0.34	360.89
19000471	J2	24508210	66020000	8.49	1	8.49
19000498	J1	24508210	66020000	107.1	1	107.1
19000471	J1	24508210	66020000	191.44	1	191.44

Sheet 2

Document Number	Tax Code	Account	Input_#2_Account	Amount in local currency	percentage	Amount
19000498	J2	24508210	66020000	0.45	1	0.45
19000477	J1	24508210	65040000	376.42	1	376.42
19000477	J2	24508210	65010000	1061.44	0.33	350.28
19000477	J2	24508210	65010000	1061.44	0.33	350.28
19000477	J2	24508210	65010000	1061.44	0.34	360.88
19000471	J2	24508210	66020000	8.49	1	8.49
19000498	J1	24508210	66020000	107.1	1	107.1
19000471	J1	24508210	66020000	191.44	1	191.44

[ShankerV]

Hi @phoebe_90 

One way of doing this.

Note: Use select tool to remove the record id column and temp column which is used for calculation purpose.

Many thanks

Shanker V

[phoebe_90]

Is there a formula for that last row? If the row is not always equal 3.

[OllieClarke]

Hi @phoebe_90 

This approach will update the final row (assuming there's more than 1 row) to minus all previous rows from the amount in local currency; otherwise it will leave the rows alone. 

This will be dynamic for any number of rows.

Hope that helps,

Ollie

[ShankerV]

Hi @phoebe_90 

Please find the modified workflow to accommodate the request which will work DYNAMICALLY where there might be more than 3 rows of same Document number and Tax code.

Many thanks

Shanker V