Function to subtract from totals when a checkbox is ticked?

Question

Level 1

8 points

Function to subtract from totals when a checkbox is ticked?

Hello there, I am hoping someone can help me out. I use numbers to manage my transactions with bills. I have the name, date to be paid and amount to be paid, as well as a total amount remaining with checkboxs next to each item. When I tick a box it subtracts the amount I pay from the total left for the month. This has worked amazingly well for me to keep track of money. But there is one small addition I would like to add, however I don't know if it's possible and I haven't found a way to do it. Here is what I'm trying to accomplish, Lets say box 1 has the name, date to be paid, amount to be paid and a checkbox beside it. I want to link box 1 to a different column/row, in this new column/row I want to have the total balance on the card. When I tick the box every month I want it to subtract the amount from the amount to be paid box from the total balance. That's easy enough, however I want it to update the total balance to reflect my payment after I uncheck the box. For example:

BOX1 Bill | 1st| $50| CHECKBOX-UNCHECKED | Total Balance - $100

BOX1 Bill | 1st| $50| CHECKBOX-CHECKED | Total Balance - $50

BOX1 Bill | 1st| $50| CHECKBOX-UNCHECKED | Total Balance - $50

Everytime the checkbox is checked then unchecked it would subtract the amount beside the checkbox from the total balance and remember it, not undo it. I have a similar setup now for the total payments for the month, but when I uncheck the boxes it adds the total back from the sum of off the amounts. Which is perfect for that and 90% perfect for what I'm wanting to achieve. I just need to have it not add the sums back when I uncheck the box.

Is this even possible in numbers (I use numbers in my webbrowser on pc and read it from my iphone by the way)? It's not the most crucial thing to have, but it would add another layer for me to control my finances better. Thank you guys for reading this and any help you can provide!

null-OTHER, Windows 10, Windows 10 pc, Iphone 7+

Posted on Jan 18, 2018 4:50 PM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

60,023 points

Jan 19, 2018 2:49 AM in response to Scaryjam8234

HI SJ,

"I just have no idea what I’m overlooking to make it happen…"

What you are overlooking is that formulas act on the current state of the cells they reference, not on the action that creates that state. They have no memory of previous states, or of previous results.

If you want to use checkboxes to record payments and trigger balance changes, you will need one checkbox for each payment to be made on each item. If the box is checked, that payment has been made. If the box is not checked, that payment has not been made.

Columns A, B and C contain entered data.

Option: B can calculate the annual total from the monthly payment: B2: C2*12

OR C can calculate the required monthly payment to clear the amount in B in one year: C2: B2/12

Columns D to O contain a checkbox for each monthly payment on each item.

P2: IF(OR(LEN(B2)<1,LEN(C2)<1),"",COUNTIF(D2:O2,TRUE)×C2)

Counts the number of payments made (checked boxes) and multiplies by the payment amount in C2.

The IF, OR and LEN parts are to prevent the calculation taking place until there are values entered in columns B and C of 'this row'.

Q2: IF(LEN(B2)<1,"",B2−P2)

Calculates the amount remaining to be paid by subtracting the amount paid to date (P2) from the original amount (B2). IF acts as a switch to prevent this calculation until there is a value entered in B2.

You might get closer to what you want by replacing the checkbox with a stepper cell. These can be set to a series of integer steps (0,1,2,3,4…) with each step indicating another payment, or in steps of the value of each payment (eg. 0,50,100,150,200…). In either case, the steps must be all of the same size. If your (monthly) bills all start in the same month, then the first model stepper for each should display the same number when that payment has been made for the current month. If the starting months are staggered, it will be less easy to tell if the stepper is showing 'this month' has been paid or 'last month' was paid.

Regards,

Barry

Reply

Answer 2

Scaryjam8234 Author

Level 1

8 points

Jan 18, 2018 9:53 PM in response to Yellowbox

My goal is to subtract the amount paid each time a box is ticked, when the box is unticked it neither adds nor subtracts from the total balance. Assuming it starts at 100 when the box is ticked it should subtract the amount paid, which is a fixed amount at 50 for example. The total should be 50 at that point. Then when the box is unticked it remains at 50, next time I tick the box it should subtract the fixed payment which would result in a 0 balance.

I have something setup similar but it’s setup with an if statement, if box is ticked add X if box is unticked subtract X but that reverses the total back to what it was before I ticked the box, so this cannot work. However it is half of what I’m trying to do.

I’m sure there’s some way to do something like I want, even if it includes a couple more steps. I just don’t know enough about word processing applications and functions to know exactly what I need or how to explain it to someone who knows how to do it, but I just have a hard time explaining it.

What if I say it this way, every time I check the box it subtracts a fixed amount from a different item. When it is unchecked nothing changes, when it is checked it subtracts again.

I would provide screenshots to explain better but because it deals with my personal finances I don’t feel comfortable showing that, if it is still unclear tomorrow I will make a mock document with fictional information to paint a clearer image as to what I’m trying to achieve.

Reply

Answer 3

Scaryjam8234 Author

Level 1

8 points

Jan 18, 2018 11:29 PM in response to Yellowbox

See if this helps. This is what I have setup for my bills without all my information. What I’m trying to do is if bill 1 box is checked it subtracts the payment($50) from the bill 1 balance at the bottom. Then when I uncheck the box I want it to remember it subtracted the payment and change the balance from $500 to $450 even if the box has been unchecked. Then next month when I go to pay the bill again and I check the box it subtracts the payment ($50) and the total balance is $400 at that point. Then I can repeat ever month with it remembering each time it subtracted the value ($50) from the month prior. I’ve figured out how to subtract the payment, but I haven’t figured a way for it to remember the subtraction and keep the value when I uncheck the box.

I messed around with the if function saying if checked product of the balance is -50 and that works but when I uncheck the box it adds the $50 back, if I put both iftrue and iffalse commands as -50 it changes the value to 450 but it stays at 450, it doesn’t subtract the 50 again it just subtracts 50 from the initial value. Which is what I’m trying to figure out is if it’s even possible. I’ve looked through every function multiple times but nothing sounds like it will do what I’m wanting. But as I said I’m not that well versed with this stuff, I’m sure if it’s possible it isn’t hard I just have no idea what I’m overlooking to make it happen if it is possible.

Reply

Answer 4

Barry

Level 9

60,023 points

Jan 19, 2018 6:18 PM in response to Scaryjam8234

Hi SJ,

Thanks for the check mark.

When you're examining the formulas more closely, remember my notes that they are shown in two parts—the bold part is the core formula, the part that does the actual calculation; the non-bolded part is the part that checks if the data to make the calculation is in place yet, and lets the core formula do its thing only when there is data to crunch.

"I see the possible problems with it, that if I make a payment more than I have entered in the monthly payments column that I cannot accurately reflect the total balance remaining. A work around is to manually edit the balance in the first column if that situation arises, while is fine with me."

That edit would work. A better one might be to insert a new column between December and Total paid in which to record the amounts of any extra payments. For lines with more than one extra, you would need to do the addition and enter the sum of the extras.

Here's a revision of my table above, with a new column inserted to the left of Total Paid.

To insert a column here, click on any cell in Column P (Total Paid), then press option-left arrow. The new column will become column P; the 'old' P and Q will become Columns Q and R.

Formula in Q2:

original: IF(OR(LEN(B2)<1,LEN(C2)<1),"",COUNTIF(D2:O2,TRUE)×C2)

Revised: IF(OR(LEN(B2)<1,LEN(C2)<1),"",SUM(P2,COUNTIF(D2:O2,TRUE)×C2))

Bold parts added. I used SUM(a,b) rather than a + b as SUM will ignore text values in column P cells, but the addition operator ( + ) will choke on them.

Regards,

Barry

Reply

Answer 5

Yellowbox

Level 7

21,939 points

Jan 18, 2018 9:20 PM in response to Scaryjam8234

Hi Scaryjam,

I am unsure of your overall aim.

Checkbox never ticked = Unpaid

Tick a checkbox = Paid.

Untick a checkbox = ?

Regards,

Ian.

Reply

Answer 6

Yellowbox

Level 7

21,939 points

Jan 18, 2018 10:53 PM in response to Scaryjam8234

Hi Scaryjam,

a mock document with fictional information

A screenshot will be a big help! I look forward to seeing it.

Regards,

Ian.

Reply

Answer 7

Scaryjam8234 Author

Level 1

8 points

Jan 19, 2018 4:40 PM in response to Barry

You sir are a genius! I had assumed there was no memory of cells after looking through all the formulas, since there wasn't anything that looked like it made reference to past values. However your method and the picture you provided were perfect solutions for me. I managed to make the table with all the debts and the formulas worked perfectly, though it took me some time to learn to type them out correctly (as I said I am not that intelligent with the program.) But I did manage to get it all working.

I see the possible problems with it, that if I make a payment more than I have entered in the monthly payments column that I cannot accurately reflect the total balance remaining. A work around is to manually edit the balance in the first column if that situation arises, while is fine with me.

As far as payment dates for the months. I pay all my bills a month ahead at the end of each month. For example on January the 28th I pay all the bills due for Feburary, so them not being in sync isn't really an issue.

I cannot thank you enough for helping me out, I have been trying to find a solution and make this work for the past few months and it seems like nobody online has asked or provided information on how to do it. I experimented with formulas for the longest time trying to figure it out myself, but my level of education on the program isn't high enough to get the complexities of what I needed to. I only loosely understand what the formulas you provided do. But that's okay with me, your solution worked the way I wanted it too with little quirks and will help me better keep track of balances I owe at places.

Thank you again!

Reply