Hi, I am trying to do a formula involves with subtraction from the cell above. Original: AB1Transaction#Amount2 AAA 4,0003BBB2,0004CCC-2,0005DDD3,0006EEE-3,0007FFF6,0008GGG-6,000 After: the new column in C is where the result would be from column D. ABCD1Transaction#AmountNet Zero Trans.Calculation in Excel in C (don't need this column)2AAA4,0004,000=IF(OR(B2+B1=0,B2+B3=0),0,B2)3BBB2,0000=IF(OR(B3+B2=0,B3+B4=0),0,B3)4CCC-2,0000=IF(OR(B4+B3=0,B4+B5=0),0,B4)5DDD3,0000=IF(OR(B5+B4=0,B5+B6=0),0,B5)6EEE-3,0000=IF(OR(B6+B5=0,B6+B7=0),0,B6)7FFF6,0000=IF(OR(B7+B6=0,B7+B8=0),0,B7)8GGG-6,0000=IF(OR(B8+B7=0,B8+B9=0),0,B8) Please help! Thanks,

Subtraction from cell above

Hi,

I am trying to do a formula involves with subtraction from the cell above.

Original:

	A	B
1	Transaction#	Amount
2	AAA	4,000
3	BBB	2,000
4	CCC	-2,000
5	DDD	3,000
6	EEE	-3,000
7	FFF	6,000
8	GGG	-6,000

After: the new column in C is where the result would be from column D.

	A	B	C	D
1	Transaction#	Amount	Net Zero Trans.	Calculation in Excel in C (don't need this column)
2	AAA	4,000	4,000	=IF(OR(B2+B1=0,B2+B3=0),0,B2)
3	BBB	2,000	0	=IF(OR(B3+B2=0,B3+B4=0),0,B3)
4	CCC	-2,000	0	=IF(OR(B4+B3=0,B4+B5=0),0,B4)
5	DDD	3,000	0	=IF(OR(B5+B4=0,B5+B6=0),0,B5)
6	EEE	-3,000	0	=IF(OR(B6+B5=0,B6+B7=0),0,B6)
7	FFF	6,000	0	=IF(OR(B7+B6=0,B7+B8=0),0,B7)
8	GGG	-6,000	0	=IF(OR(B8+B7=0,B8+B9=0),0,B8)

Please help!

Thanks,

Workflow

Accepted answers

AmeliaG

Hi @yzhang316,

Thanks for your question! To achieve your desired result, use a 'Multi-Row Formula' tool. Please see the below screenshot and attached workflow for details on how to set up.

net zero trans.png

Net Zero Trans.yxmd

All comments

tcroberts

You'll want to look into the Multi Row Formula Tool.

You can do what you're looking for with the following expression:

[Row-1:Net Zero Trans.] + [Amount]

This will take the value above it in the Net Zero Trans. column, and add the Amount in the current row. You could turn this to subtract if you wanted easily.

Then, just remember to configure the tool to create a New Field, and name it Net Zero Trans.

Let me know if you need more assistance,

Cheers!

EDIT: Here's a screenshot of the formula in action:

Spoiler

AmeliaG

Hi @yzhang316,

Thanks for your question! To achieve your desired result, use a 'Multi-Row Formula' tool. Please see the below screenshot and attached workflow for details on how to set up.

net zero trans.png

Net Zero Trans.yxmd

yzhang316

Thanks for the reply!

This is where I got stuck actually. I was able to add the new column and do the addition formula, but I don't do how to do the "OR" formula in there. In excel it would be =IF(OR(B2+B1=0,B2+B3=0),0,B2), if B2+B1=0 or B2+B3=0 then 0 else B2.

yzhang316

Thank you! your magic worked and I learn something new today!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703