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

Msman

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.

Paul Newcome

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

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

That is because you have

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

When it should be

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

Msman

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

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

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

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

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

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

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

Appreciate the effort in helping with this :)

Paul Newcome

Happy to help. 👍️