need a formula function that takes pos & neg data from separate cols and displays results in another

Question

I have an 8 column spreadsheet that I'm trying to set up in order to maintain my church's financial records.&nbsp; Column b is for deposits.&nbsp; Column d is for expenses/withdrawals.&nbsp; What formula do I enter in column g in order to get a running total line by line when g adds b and subtracts d?

hansvogelaar · Answer

Mike_GilmanRabakami1&nbsp;
Let's say the data begin in row 2.
In G2: =B2-D2
In G3: =G2+B2-D3
Fill down from G3

patrick2788 · Answer

Mike_GilmanRabakami1&nbsp;I think there are a few ways to approach this task: 1) Use dynamic arrays or 2) Go with a table.&nbsp;For the attached workbook I've simplified the Check Register Ledger template offered by Excel (File | New - search for templates).&nbsp;This is a dynamic array solution:A negative sign is added before the withdrawal dynamic item because ledgers often do not list withdrawals as negatives but as positives (This template does not use negatives):=LET(
    transactions, Deposit + -Withdrawal,
    Accumulate, LAMBDA(a, v, a + v),
    SCAN(0, transactions, Accumulate)
)The Withdrawal and Deposit columns have been defined as dynamic items. Formula is placed in G.&nbsp;Also, I've attached the less exciting table solution.&nbsp;&nbsp;&nbsp;

mike_gilmanrabakami1 · Answer

Hans my data actuaoly begins on line 3 so I just took your formula and changed the line and cell # by one but the solution didn't work.HansVogelaar&nbsp;

hansvogelaar · Answer

Mike_GilmanRabakami1&nbsp;
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Forum Discussion

need a formula function that takes pos & neg data from separate cols and displays results in another

4 Replies

Resources