Change cell value based on checkbox in Google sheets

Question

I'm using Google sheet to manage my budget (see sample below) where I add each of my expenses as a single entry (yes, sounds like a lot of work). I sometimes split the expense with my roommate but then I have to add the value and divide by 2 everytime.

I was thinking if I could use a checkbox next to the value that will automatically divide the expense number by 2 when I check it. Is this possible?

I'm open to simple suggestions other than the checkbox to automatically update the value. Thank you.

Answer 1

Using simple IF formula you can just check if the checkbox is true, if it is then it will divide the current value on column C by 2. Otherwise it will remain blank.

Formula:

=IF(D1,C1/2,"")

Drag down to other cells.

Result:

---

---

Suggestion, Alternate solution:

If you'd like you can make a table with a column for your roommate, instead of editing the actual column so you can see both values. And use this formula:

=if(NOT(D2=""),E2/2,E2)

You have a column for per head contribution/split. If the cell on roommate is blank then it will stay as the total value, if roommate has an additional then it will be added to total and split it by 2.

Or using arrayformula:

=arrayformula(if(NOT(D2:D=""),E2:E/2,E2:E))

Works the same as above you just have to fill the enter the formula in the first cell no need to drag down and it will automatically expand to rows/cells below just make sure that below cells are empty or it will return an error.

Additional - Using same cell

As you've mentioned in the comments. Here's a way to divide the original value without using another cell to store it. (Not recommended)

Formula:

=VALUE/IF(D1,2,1)

example:

=1000/IF(D1,2,1)

Result:

However, I do not recommend this. It is still best to make use of another cell to store the original value before making calculations to it.

Also, using this formula you have to change the value from the formula and not on the cell otherwise you will replace the actual formula.

Answer 2

You can try array approach-

=ArrayFormula(IF(D1:D,C1:C/2,C1:C))