General Income and Expense Sheet - Referencing Cell above, and using Column Formula
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
-
Try something like this instead:
={Total Funds Available} - SUMIFS(Payments:Payments, Row:Row, @cell<= Row@row) + SUMIFS(Deposits:Deposits, Row:Row, @cell<= Row@row)
-
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
-
That is because you have
SUMIFS(.....) - SUMIFS(.....)
When it should be
SUMIFS(.....) + SUMIFS(.....)
-
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), ""))
-
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), ""))
-
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.
-
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?
-
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.
-
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.
-
Thanks for pointing that out. And yes, it worked.
Appreciate the effort in helping with this :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!