How to only record 50% of each transaction on a specific account?

Is there a way to have Tiller record only half of each transaction for certain accounts? ie. If I import an account where there’s a $10 charge for an Uber, I’d like it to show up as $5 Uber charge in my monthly budgeting and reports. Is this possible somehow?

There is, @markp!
Check out the new Splitter in the Tiller Labs add-on.

Cheers,
Randy

Thanks, but I’d love to be able to do this automatically for every single transaction tied to a specific account. It looks like Splitter only allows you to split individual transactions manually?

@markp

I have been thinking about your situation. I think you might be stuck doing this on a manual basis. I like @randy idea about using the splitter tool as it leaves a trail. You might consider going to the Transactions tab number and merely diving the number by two but I would develop a method to track which numbers you are changing in case you ever want to know in the future what you did. You probably need to give some thought to modifying the balance for the account as well as the transactions. If not, your net worth will not be accurate but maybe this is immaterial in the whole scope of things. How many transactions would you be modifying per week/month? If the number is small, manual modification may not be as time intensive as you might have previously thought. Let us all know.

Blake

Thanks for responding, Blake.

It’s about 30 transactions a month, so I would prefer not to have to do them manually.

That said, I want to make this change on all past and future transactions to one particular card only, so hopefully it should be a relatively simple tweak somewhere in the spreadsheet. Do you have any suggestions on where I might look in the spreadsheet to modify the balance as well as how transactions get reported to the transactions sheet?

Hi @markp,
Here’s one suggestion.

You could add a new column to your Transaction sheet. Put this formula in the top row of the new Column. It will create an adjusted amount of half the original amount when the Account Name matches “Your Credit Card”. Adjust the formula as needed:

={"Adjusted Amount";ARRAYFORMULA(IF(ISTEXT(H2:H),IF(H2:H="Your Credit Card",E2:E/2,E2:E),IFERROR(1/0)))}

This formula will label the Column “Adjusted Amount”.
It assumes the Account name is in Column H and the Amount is in Column E.
Change the H’s and E’s to match your sheet if necessary.
Also, change “Your Credit Card” to match the account you want to adjust.

You would then need to adjust any formulas on other sheets you want to use this adjusted amount on so they lookup the “Adjusted Amount” column, not the Amount column.

Would that work for you?

WIth Google Sheet formulas, you can automatically write data into a cell that you also want to manually write to. Thus, the need for the create a separate column.

Great option Jon, @jonorlin @markp

I assume he would want the 50% amount to flow everywhere. Would it take a lot of work to update all these formulas? Also, what would he need to do so that 50% of the balance flows throughout his sheet? I don’t know for sure, but this seems like a lot of updating will need to be done. You will always be wondering whether you got every formula and then there will be the issue of having to update all of this if you load new versions of the templates when they come out. Any additional comments on all of this?

Blake

Hi @Blake,
Since all the relevant sheets pull from the original Amounts columns, they won’t break by adding the Adjusted Amount column.

It would take some digging to adjust the sheets that @markp might want adjusted to pull from the Adjusted Amount column. You would have to understand how the hidden sections of the sheets work.

I’d probably keep all the original sheets, and make copies (with Adjusted names) for the modified sheets. That would keep things working just in case.

I’m not sure what Mark’s original motivation is for this. Is the goal just seeing a monthly budget with half the amounts (which seems odd)? It might be better just to cut the budget amounts in half.

There might be a better way to solve this with just one modified sheet and not changing the Transactions sheet.

It’s great that Tiller and Google Sheets lets you create customized individual solutions. Everyone has their own needs. But i’m not sure how many others are looking for this type of solution.

Thanks Jon - your suggestion worked perfectly. Now, as Blake surmised, the challenge is to get those transactions to flow through the rest of the document.

My motivation for this is pretty simple. My partner and I share a credit card through which we make any household/joint transactions. Since we both pay it down in equal measure (ie. 50/50), it feels appropriate to only report 50% of the card’s activity (and balance owing) to my own personal budget.

I’m new to Tiller so I haven’t fully acquainted myself with the hidden sections of the sheets. I’ll do some nosing around tonight/tomorrow, but if anyone more knowledgeable has time to share insights on what I might need to adjust to get this working everywhere, that’d be hugely appreciated.

And thanks all for your help so far!

Glad that worked @markp.

Your use case makes a lot of sense now. I learn something new every day.

Once you have the Adjusted Amount column working in the Transactions column, you can then make Adjusted copies of sheets you might use. Look for the word Amount in the hidden columns and change it to Adjusted Amount. I’m not sure but the space in Adjusted Amount might cause some formulas to break, so you might change the column name to just Adjusted if you see that happening.

Thanks!

Do you think it would work to change the original Amount column name to something else and change Adjusted Amount to Amount? If so, do you have any sense where I would do that? I haven’t yet figured out where the rest of the columns in the Transactions sheet are being populated from…

No. Tiller needs a column called Amount to enter the Transaction amount info.
If you change the original column to something else, it won’t feed in new Transaction amounts.
And if there is a formula in that column, I don’t think it will work.

@markp @jonorlin

This thread has confirmed my suspicions and that is that you are going to pay a price for automation. You are talking about one manual change per day to the Transactions tab. That will take two seconds and then you are done. It is going to take too much work to automate and it will be an ongoing job to maintain the automation.

If it were me, I think I would manually change the numbers to 50% on the Transactions tab. Then, regarding balances, I would set up a contra-account for 50% of the credit card balance. This contra-account would be a negative liability account and would be a manual account. Its amount would be 50% of whatever the credit card balance is. I would code this contra-account the same way as the credit card is coded so that is appears right next to the credit card balance. If you are interested in this approach, please let me know and I can set up this contra-account in my sheet and post a few screens shots that should be helpful.

Blake

I think your approach makes a lot of sense @Blake.
There might be some hidden gotcha’s to my suggestion that would take time to resolve.

@jonorlin @markp

See the attached as to what the two relevant tabs would look like.

Blake

Thanks Blake - I really appreciate it!

I’m going to explore editing the sheet to see if I can truly automate this process, but the contra approach is something I’ll fall back on it if it does indeed prove too complex.