Checkbook "Cleared" Balance

Options
SickFreak
SickFreak ✭✭
edited 11/20/23 in Formulas and Functions

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!

Tags:

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Options

    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.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • SickFreak
    Options

    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
    Options

    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
    Zachary Hall ✭✭✭✭✭✭
    Options

    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.

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • SickFreak
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!