Forum Discussion
Checkbook Register with Automatic Round-Up to Savings Feature
That's not a single question, for what it's worth. It's a whole bunch of questions.
But that quibble aside, may I suggest you check out the Excel function ROUNDUP, read about it in Excel help....figure out what kind of "ROUNDUP" you are asking for--up to the nearest dollar, up to the nearest 10 dollars, the nearest 100? Doing it only to the nearest dollar seems so trivial as to not be worth it.
So check out that function. Play with it a bit. Create your own rounding up formula--it's a fairly easy one to do, and since you already appear to have a check register working, adding this feature should be a good learning experience.
As for the rest of that, once you've gotten ROUNDUP to do it's job as you want it to, come back and upload a sample of your spreadsheet as it exists at that point.
Hello mathetes,
I took some time and was able to get the Roundup formula to do some of the simple tasks I wanted the Roundup formula to complete. Although I couldn't figure out how to get the information to display on a separate page. So, I thought I'd try a simple Value Lookup, and use that as a constant update number on the Savings Account Page then just enter Transfers Manually, but that was unsuccessful. Here is what I am currently working on though.
I still would like to accomplish what I mentioned in the first reply if you see it possible.
Thanks,
DC_BarTroNA further revision, mostly cosmetic but with improved clarity and efficiency in mind. What I've done is put the date and time fields in the middle. Did this so that when you do a Savings Account transaction off to the right, it still is clear that the date and time have been used, even though it's blank on the left. I am assuming you make deposits to and withdrawals from the savings account independent of checking transactions.
I realize, of course, that I'm suggesting an altogether different way to accomplish what you were wanting to do. Turning what would be two "clearly distinct" sheets into a combined one is (some might say) a radical revision. It does seem to me though that unless your savings account is extremely active and needs a separate sheet, your main "presenting issue" of taking the rounded up delta and adding it to the Savings Account is really more neatly and clearly handled by combining the two records rather than by generating a separate sheet.
Anyway, see what you think here. I've entered two 'test" Savings-only transactions.
By the way, you are to be commended for the level of sophistication shown in the formulas in your worksheet. You clearly know your way around Excel.
Kept thinking about it, and came up with a minor revision in part just to illustrate a simple way to change the ROUNDUP formula itself, should you decide to be more aggressive in the amounts you devote to Savings.
This uses a named range (set up on the second worksheet, which I've named "Bus Table" to indicate the fact that you can add other tables there. There's a named range created, called "RoundUpDeg" --to enable easy changes to the degree you do your rounding. Change the value in cell B1 to -1 and your rounding goes to the nearest 10 dollar figure, greatly increasing you savings rate, yet still (hopefully) not breaking your checking account balance. The formula need never be changed, just the value in that single cell.
=ROUNDUP(D5,RoundUpDeg)
I'm a firm believer in single databases, wherever possible, using attributes in one or more columns to differentiate for reporting and summarizing.
So here, for instance, the truth is that you can see the rounding up and transferring to Savings of $xx.xx is really part of the same, single transaction as that recorded in the Checking side of things. (That's evident even in the fact that in your two sheets, for those rounded-up transactions, the date and time stamps are identical.)
It's really one transaction, albeit and extended transaction, one with multiple parts affecting two accounts. But at bottom, one transaction. So it can easily be recorded on one line as both a Checking transaction AND a Savings transaction.
See the attached for how this resolution would work.
