Expanding on a working formula.

[mjhowe29]

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.

[#2]

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.

[#3]

@mathetes 

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 :grinning_face_with_sweat:

Many thanks.

[#4 · Accepted solution]

@mjhowe29 

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

[#5]

Hi Mathetes,

You're a life saver!! That works perfectly, thanks for the help. I didn't want to show a starting balance, so I just hid that row. The purpose of this is to look at income & expenditure (separate sheet), and then the one I've been asking about will help people better understand how their money is being used throughout the month. Some people have multiple payments, especially benefits, coming in throughout the month. The running balance in the tracker, or ledger, gives a clear view of how much is remaining between pays. That means that it is easier for people to plan, and alter direct debit dates accordingly, so that they always have money each week.

Thanks once again for all your help on this.

[#6]

@mjhowe29 

I didn't want to show a starting balance, so I just hid that row. The purpose of this is to look at income & expenditure (separate sheet),

Let me question your reasoning just a bit:  the column heading is "Balance" -- and that doesn't start in a vacuum. It might well be that, for some people, the starting balance is a nice positive number. I suppose some might even have a starting balance that is negative; but the point is that there IS a starting balance, and the "running balance" begins from that starting point. So show it. The "balance" doesn't fully make sense without that starting reference point.

Some people have multiple payments, especially benefits, coming in throughout the month. The running balance in the tracker, or ledger, gives a clear view of how much is remaining between pays. That means that it is easier for people to plan, and alter direct debit dates accordingly, so that they always have money each week.

So you're creating this for other people to use? Presumably other elderly people (given that "pension" is one of the items). [For the record, I'm 81 years old myself.] Creating something like this for other people is a big challenge, especially the elderly (I was fortunate to have had the opportunity to work for IBM, where I learned programming, back a decade or so before home computers came on the scene, so I've been computer literate all of my adult life). Or are you creating it to use yourself as a counselor, working with them, but you'll be making the entries.....?