Forum Discussion
Solved
Expanding on a working formula.
I am using the following formula which will look at income sources in a table and adjust a balance column accordingly with each entry:
=SUMPRODUCT([Amount], --([Date]<=[@Date]), ([Type]<>"Income") * (-1) + ([Type]="Income"))
If the "Type" matches "Income", it will register the input value as a credit and will appear as such in the running balance. If it does not, the input value will subtract from the running balance.
I am trying to evolve this balance tracker to include other forms of credits, such as Benefits, Pensions etc, so I want the formula to look at these as well but I can't quite work it out.
I'd really appreciate any help.
TIA and all the best.
It makes a lot of sense. And using the type of formula you were using makes it both more complicated and less flexible than you'd like in the long term. Speaking from experience, I know that any attempt at tracking income vs expense is always going to run into unexpected and new forms of income or expense that also need to be handled. Tax Refund, Gifts, for example, are things you might want to categorize separately, and so on.
SO, the resolution I recommend is a table with one column representing the types and the second column representing the factor that makes it positive or negative.
The list on the left also, conveniently, serves as the source for the drop down in the ledger.
The formula then can be simple, and, since the variables are not hard-coded in the formula, far more flexible:
=IFERROR(F2+(D3*(VLOOKUP(B3,Table1,2,0))),"")See the attached
- My suspicion is that you may be making this more complicated than it needs to be, but seeing only the formula, not the context--i.e., the actual spreadsheet--makes it only a suspicion, an assumption.
Is it possible for you to share either the actual spreadsheet, or, if that's confidential, a mockup? You can post a workbook on OneDrive or GoogleDrive, and paste a link here that grants access to the file.Here is the basic table template. I want any type that equals "Income" "Benefits" or "Pensions" in the drop down to display as a credit in the balance column. You can see that I start with a wage category, which is in the type "Income", and I have a starting balance of 1000. Rent is the next entry, which is deducted as it should be, with a deduction of 600, taking the balance down to 400.. However, the next type comes under "Benefits", which should also be a credit. This should then push the balance back up to 900. Instead, the amount of 500 is being calculated as a deduction, taking the balance down to -100, and so on.
The formula below works for everything where the type is "Income", but if I try to add the other types to it, I can't get it to work at all.
I hope this makes sense 😅
Many thanks.
It makes a lot of sense. And using the type of formula you were using makes it both more complicated and less flexible than you'd like in the long term. Speaking from experience, I know that any attempt at tracking income vs expense is always going to run into unexpected and new forms of income or expense that also need to be handled. Tax Refund, Gifts, for example, are things you might want to categorize separately, and so on.
SO, the resolution I recommend is a table with one column representing the types and the second column representing the factor that makes it positive or negative.
The list on the left also, conveniently, serves as the source for the drop down in the ledger.
The formula then can be simple, and, since the variables are not hard-coded in the formula, far more flexible:
=IFERROR(F2+(D3*(VLOOKUP(B3,Table1,2,0))),"")See the attached
