Accounting: Cleared Balance Column

[JJerrell]

Hello, I'm happy (yet not surprised) to find such a developed community. I appreciate you taking the time to read into my problem!

Scenario: I have a typical transaction register that looks and feels exactly like a paper checkbook. The balance column uses SUMIF and OFFSET to check the previous balance, add the credits, and subtract the debits. The main difference from a paper check register is my cleared balance column which is what pulled me into the depths of the internet for an answer. This column checks for a positive integer in a specific column in the same row, if the integer is positive/true, it adds or subtracts the numbers in that row to/from the previous number in the cleared balance column. If the integer is 0/empty (false), then that cell inherits the previous cleared balance cell.

The issue is that I can't figure out how to incorporate what I'm using in that column with the logic being used in the normal balance column.

With the short backstory in your mind, I think it best to provide an example: https://www.excelforum.com/attachmen...1&d=1508808879

As you will see, excel is not at all happy with what I've done in that table. I've put the initial deposit as the balance in the first cell to make the following formula work, then wrote an "IF x(true) THEN y+z-a ELSE y[-1]". (With [-1] meaning the previous cell in that column as described above)

Truthfully, I copied the formula in the balance column from another source. I have a pretty good idea of what it's doing, but I just don't understand it well enough to also apply this "IF X is True" part to it.

[sktneer]

Hi,

Welcome to the forum.

Try this...

In I5

Please Login or Register  to view this content.

and copy it down.

[JJerrell]

That solution is simply beautiful. Can't believe it was that easy

Thank you

[sktneer]

You're welcome! Glad it resolved the issue.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

You may also say thanks to those who have put their time and efforts to help you in this forum by clicking the Add Reputation link under their posts, another way to say thanks.

[JJerrell]

Sorry for the late response, I had some happenings in the family that took me away for a while.

The formula definitely works, but I was hoping to solve the issue where I have to enter something manually in the first column to get it to work. When you convert it to a table, it gives an 'Inconsistent Calculated Formula' error. If you look at column H, you'll see the trick (offset) that was used to prevent that error.

Again, the column H trick isn't of my own invention, I just adapted it to fit my table. I'm not sure how to add this new level of complexity to the already complex formula

[sktneer]

Hope all is well now.

I am not sure what are you trying to achieve and missing the logic for having the offset formula in column H.
I believe you simply need these formulas...

In H5

Please Login or Register  to view this content.

In I5

Please Login or Register  to view this content.

[JJerrell]

Thank you. That helped quite a bit.

I had to make a couple small changes (and adjust the numbers to match the actual sheet I'm using it in). But here is what I ended up with in the I column:

Please Login or Register  to view this content.

It works very well. Thanks again!

[sktneer]

You're welcome!
Please pay some attention to Post#4.