How to subtract values from previous row values?Hi all
Can someone help me how to formulate a formula that would subtract Column 2 to the previous value in Column 1? It should look like this. The column Equation only shows how the subtraction of values should be.TIA
Automation and ScriptingRe: How to subtract values from previous row values?You can use the LAG function:
:Column 1 - Lag( :Column 2, 1 )
Re: How to subtract values from previous row values?Then you need some conditional logic. Something like:
If( :Column 2 != Lag( :Column 2, 1 ) | Row() == 1,
:Column 1,
:Column 2 - Lag( :Column 1, 1 )
)
Re: How to subtract values from previous row values?Hi, how about if I wanted to retain the row value before the values in Column 2 changes? Like this one
Re: How to subtract values from previous row values?Hi, I have a follow up question tho. What if I needed to add the values from Column 1 to be subtracted in Column 2?
For example,
| Column 1 | Column 2 | Result | |
| 10 | 20 | 10 | |
| 2 | 20 | 8 | |
| 5 | 20 | 3 | |
| 3 | 20 | 0 | |
| 2 | 15 | 5 | |
| 1 | 15 | 13 | |
| 3 | 15 | 12 | |
| 4 | 15 | 5 | |
Re: How to subtract values from previous row values?Could you double-check your numbers here? I'm not sure what's going on when :Column 2 == 15.
But I think this is what you want (based on David's script):
If(
:Column 2 != Lag( :Column 2 ) | Row() == 1, :Column 1,
Row() == 2 | Lag( :Column 2, 2 ) != :Column 2, :Column 2 - (:Column 1 + Lag( :Column 1 )),
Lag( :Result ) - :Column 1
);
Re: How to subtract values from previous row values?Hi
Column 2 are given values. Apologies, my table is a bit messed up. Values within the same row should not be subtracted on the added values. It should be
| Column 1 | Column 2 | Result | (should be computation) |
| 10 | 20 | 10 | same value in Column 1 |
| 2 | 20 | 10 | Column 2 - Column 1 row 1 (20-10) |
| 5 | 20 | 8 | Column 2 - Column 1 rows 1 to 2 (20 - 10 +2) |
| 3 | 20 | 3 | Column 2 - Column 1 rows 1 to 3 (20 - 10 +2 +5) |
| 2 | 15 | 2 | |
| 1 | 15 | 13 | |
| 3 | 15 | 12 | |
| 4 | 15 | 9 | |
Re: How to subtract values from previous row values?In that case:
If(
:Column 2 != Lag( :Column 2 ) | Row() == 1, :Column 1,
Row() == 2 | Lag( :Column 2, 2 ) != :Column 2, :Column 2 - Lag( :Column 1 ),
Lag( :Result ) - Lag( :Column 1 )
)
