Checkbook "Cleared" Balance

Hi, new here, so thank you for the opportunity to ask. I've got a simple checkbook set up, with all the typical entries: date, amount, type transaction, etc. I have a "reconciled" column, "CLR," that I'll type in "R" when it clears the bank.

What I've been beating my head over for several hours is a formula that updates my bank balance when I reconcile with "R."

Here's what I've tried so far:

=INDEX(AutoNum:CLR, MATCH(MAX(COLLECT(AutoNum:AutoNum, CLR:CLR, =CLR@row)), Balance:Balance, 1), 13)

#NO MATCH

=INDEX([Balance]:[Balance],MATCH(MAX(COLLECT([Balance]:[Balance],[CLR]:[CLR],"R",[Date]:[Date],1)"R",1))1,13)

#UNPARSEABLE

=INDEX([Balance]:[Balance], MATCH(CLR:CLR = CLR1)1,13)

#UNPARSEABLE

=INDEX(COLLECT(Balance:Balance, CLR:CLR, CLR@row, MAX(COLLECT(Balance:Balance, CLR:CLR, CLR@row, Date:Date, Date@row))), 1)

#INCORRECT ARGUEMENT SET

I've borrowed from several others to get to these, but I'm sure I'm ordering the arguments wrong. Any help would be greatly appreciated!

Find more posts tagged with

Formulas

Accepted answers

All comments

Zachary Hall

It is possible I am missing a reason why this is the method you prefer, but I would probably use a different function rather than an INDEX/MATCH to resolve this issue.

For instance, using a SUMIF() in your Bank Balance column like this

=SUMIF([Transaction Amount]:[Transaction Amount],CLR:CLR,"R"

Feel free to respond if this doesn't resolve the issue, and I'll try to help.

SickFreak

Hi, Zach, and thank you. I tried your idea, but it still throws "#INCORRECT ARGUEMENT SET." Believe me, I prefer simple, but the reason I went with something so inelegant is because it's how it worked in Excel.

Thank you again. I'm going to keep plugging away.

SickFreak

Zach, upon further review, I added a function that at least got an incorrect value of "0".

=SUMIF([Transaction Amount]:[Transaction Amount], MAX(CLR:CLR, CLR1))

Zachary Hall

One thing that could be causing the zero value in this last iteration is if the Transaction Amount field has somehow been converted to a text value. For instance, if the transaction amount is being assembled with an IF() formula using a text "-" to be concatenated onto the number value of the Entry Amount, this would cause the information to be stored as text which has a zero value when you attempt to take the SUM().

Is it possible that the sheet is looking at the Transaction Amount as text? You could easily find this out by using a simple:

=2*[Transaction Amount]@row

If that doubles the amount of the transaction, you can ignore the above suggestion. If it gives you # INVALID OPERATION then you will need to convert the [Transaction Amount] to the VALUE() first.

SickFreak

No sir, it's not that. I appreciate it, but it's something else. I'm grimly determined now.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.