General Income and Expense Sheet - Referencing Cell above, and using Column Formula

Options

Hi Everyone,

Basically trying to create a basic income and expense sheet, moving from Excel.


Where the first row, has a fixed value and the rows after will have a formula of balance from previous row - payments from current row + deposits from current row. The formula is dragged along the Excel sheet.

Excel has only 3 columns : Payments, Deposits and Balance



Now trying to automate and replicate this in Smartsheet, using Column formulas has been a nightmare. So far based on previous queries, I have set this sheet in this particular way to replicate the Excel set up but in an automated way and used the below formulas. It has been very challenging to do so, and getting an error in some form or the other. Open to ideas, and can change sheet structure too, with more or less columns.


Thanks for the help.

MS


Previous Row Reference Column (fx) = Row@row - 1

Row Column (fx) = MATCH(Auto@row, Auto:Auto, 0)


Payment + Deposit Column (fx) = (IF(Row@row = 1, {Total Funds Available}, IF(OR(Payment@row <> "", Deposit@row <> ""), SUMIFS(Payment@row:Payment@row, Row@row:Row@row, <=Row@row) - SUMIFS(Deposit@row:Deposit@row, Row@row:Row@row, <=Row@row), "")))

Previous Balance Column (fx)= IFERROR(INDEX(Balance:Balance, MATCH([Previous Row Reference]@row, Row:Row)), {Total Funds Available})

Balance Column (fx) = IF(Row@row = 1, {Total Funds Available}, [Payment + Deposit]@row + [Previous Balance]@row)


{Total Funds Available} is a cross referenced value from another sheet, which is inputed, this number can be a hard entry as well.



Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try something like this instead:

    ={Total Funds Available} - SUMIFS(Payments:Payments, Row:Row, @cell<= Row@row) + SUMIFS(Deposits:Deposits, Row:Row, @cell<= Row@row)

  • Msman
    Msman ✭✭
    Options

    Hi Paul,

    I used the below formula :

    Balance Column (fx) =IF(OR(Payment@row <> "", Deposit@row <> ""), {Total Funds Available} - SUMIFS(Payment@row:Payment@row, [ROW#]@row:[ROW#]@row, <=[ROW#]@row) - SUMIFS(Deposit@row:Deposit@row, [ROW#]@row:[ROW#]@row, <=[ROW#]@row), "")

    As you see, it works for row 2, but as you see, the value for Row 3 and moving forward should be

    Balance(@row - 1) - Payment@row + Deposit@row

    In this case for row 3 should be :

    29307.31-1000+445 = 28,752.37

    Thanks,

    MS


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    That is because you have

    SUMIFS(.....) - SUMIFS(.....)


    When it should be

    SUMIFS(.....) + SUMIFS(.....)

  • Msman
    Msman ✭✭
    Options

    Hi Paul,

    Still not getting it. I have renamed a column to do a manual calculation to show what the actual balance should look like. Row 2 is fine, but there after, the values are incorrect.

    Also, made a slight tweak for Balance1 cell, and reflects in formula, below.

    Do I need to fix anything else.


    =IF(Row@row = 1, {Total Funds Available}, IF(OR(Payment@row <> "", Deposit@row <> ""), {Total Funds Available} - SUMIFS(Payment@row:Payment@row, Row@row:Row@row, <=Row@row) + SUMIFS(Deposit@row:Deposit@row, Row@row:Row@row, <=Row@row), ""))






  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try this then:

    =IF(Row@row = 1, {Total Funds Available}, IF(OR(Payment@row <> "", Deposit@row <> ""), ({Total Funds Available} - SUMIFS(Payment@row:Payment@row, Row@row:Row@row, <=Row@row)) + SUMIFS(Deposit@row:Deposit@row, Row@row:Row@row, <=Row@row), ""))

  • Msman
    Msman ✭✭
    Options

    Still doesn't work, get the same values. Added another column to show the balance (using the column formula), and actual balance (dragging down the manual formula), just not sure what is missing.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    That's odd. My formula is simply taking the starting number and subtracting all previous Payments and adding all previous Deposits. When I run it on a calculator using the same logic, it turns out accurate.


    Really you shouldn't even need the IF statements or the OR statement. All you should need is

    {Total Funds Available} - SUMIFS(Payment@row:Payment@row, Row@row:Row@row, <=Row@row) + SUMIFS(Deposit@row:Deposit@row, Row@row:Row@row, <=Row@row)


    How exactly is your {Total Funds Available} reference being populated?

  • Msman
    Msman ✭✭
    Options

    Hi Paul,

    I updated the balance formula, but that didn't work either, Total funds available is just a number linked from another sheet. It can just be a hard entered number.

    I broke the formula and created two new columns showing separate calculation for : SUMIFS(Payment@row:Payment@row, Row@row:Row@row, <=Row@row)

    and

    SUMIFS(Deposit@row:Deposit@row, Row@row:Row@row, <=Row@row)

    I think this section doesn't work. It essentially, just returns the values in the payment or deposit columns, and not the cumulative upto that row.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I see where the problem is. You adjusted the ranges from my original formula.

    ={Total Funds Available} - SUMIFS(Payments:Payments, Row:Row, @cell<= Row@row) + SUMIFS(Deposits:Deposits, Row:Row, @cell<= Row@row)


    I have

    Row:Row, @cell<= Row@row

    which does in fact provide the cumulative up to the existing row.


    You used

    Row@row:Row@row, <= Row@row

    Adding the "@row" portion to the range is what caused it to stop working. I missed that the first time when I started copy/pasting your formula and trying to tweak the parenthesis.

  • Msman
    Msman ✭✭
    Options

    Thanks for pointing that out. And yes, it worked.

    Appreciate the effort in helping with this :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!