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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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(.....)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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), ""))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks for pointing that out. And yes, it worked.
Appreciate the effort in helping with this :)
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!