I mentioned last week that it appears there was a formula error on the Summary page of the Savings Tracker. I took some time to look at it today, and I really don't know why it was producing strange numbers! At any rate, I decided to modify the formula to clear things up.
To solve this problem, I have two possible solutions. First, you can download the corrected version:
(Edited at 10:05 pm to include fix to the "Coupons" column as well)
Download 2010SavingsTracker(for Excel 07)
Download 2010SavingsTracker2 (for Excel 97-03)
You will then need to move your data over into the new spreadsheet. The simplest way would be to copy and paste.
But perhaps an easier, faster way is to simply make the corrections yourself to the spreadsheet. You will need to have a little Excel know-how in order to do this (or at least, not be intimidated by Excel!).
On your Savings Tracker, open the tab named "Summary" - you'll find it way left on the bottom. Look for the column titled "Discounts" – I've highlighted it for you.
You will need to change each of the cells in this highlighted row. Start with January, which is cell D2. Go to the formula bar at the top of the Excel screen and type in this formula:
=January!D4-January!D3
Click enter. Then, move your cursor to February's cell, D3, and type this formula:
=February!D4-February!D3
The rest follow suit right down the column, making sure the formula corresponds to the correct month.
=March!D4-March!D3
=April!D4-April!D3
=May!D4-May!D3
And so forth.
EDITED to add: there was sadly an error also in the "Coupons" column – thanks to the readers who pointed that out. The downloadable versions have been corrected above. If you wish to make the correction yourself, please make sure that every formula in this column reads "month!D3" and NOT "D2." Such as:
=January!D3
=February!D3
=March!D3
I apologize for this these mistake mistakes. In looking at the way I wrote the original formula, I can't tell why it wasn't producing correct numbers. But this seems to clear up the problem.
Please let me know any additional improvements or suggestions you have for this tracker. Seeing how we are into mid-February, I probably won't release any more editions of the 2010 version. However, I would love to take your ideas and expand this into an even more useful tool for 2011. Thank you!!
{ 13 comments… read them below or add one }
Too funny! I noticed the glitch when I posted my first February purchase, but I thought it was something that I had screwed up, as I added a few columns for particular things that I track (that other people pay find wierd, lol!). I LOVE this spreadsheet. I used to “pencil and paper” all my savings and it took a lot of time. Not sure why I have never put it in a spreadsheet myself (I work with them all the time- I am a bookkeeper), but yours has saved me tons of time!
Hi! Your directions were very clear and I was able to make the changes easily. However, it still seems that something isn’t quite right. I might just be looking at it wrong, but my “coupons” column for February (on the Summary page) isn’t right. Mine is showing the same number as “shelf cost” instead of my actual coupon savings. January’s is correct though. Not sure if this is just mine, or if this could have been what was causing the original problem?
Cindy,
Some of the coupons cells have d2 instead of d3 in them– if anyone is seeing this just change it to a 3 to get the coupon total carried over.
Yes; you’re correct Sarah! UGH, so sorry all!! I will make an update on here to clarify. Just you wait…2011′s Tracker will be something else after all the updates and feedback and tinkering! LOL!
Thanks ladies!!
Thank you for your work! I just wish there were a column so I could enter the tax that I spent as well.. I’ve been entering it as a negative rebate.
I just include it in with the shelf price. Feel free to add a column yourself if you wish – make it your own!! If enough people are bothered by this, I could add a column for tax in 2011.
Thank you so much for the new version! I love how I don’t have to have a separate excel spreadsheet for my rebates anymore!
What’s the best way to enter rebates and register rewards/catalinas that generate?
When you enter something as a rebate in the monthly column, do you add that to your shelf cost?
Just trying to find the best way to track everything.
I love this tracker!!
I added a column for sales tax, and next to it is a column for “net cost”, that is- what I would pay if it weren’t for pasky taxes, lol! I also keep up with my average $ per transaction, so I put in a column for # of transactions for each month on the summary page, and use it to calculate my average $ amount. I’m not spreadsheet savy enough to figure out a formula to count my transactions; I just put this number in manually each month, but it’s easy to tell since the first transaction starts on line 11 each month.
Lynette, actually to get the count of transactions is a really simple formula! =COUNT(range) should do it. For instance, if your transactions are in column D and start on row 4, I’d write the formula like this =COUNT(D4:D500) or something – for your second number, pick something WAY out there so you can make sure it captures everything. Let me know if that works!!
I’ve gotten a couple other readers who have shared that they modified it in some way or other that makes sense for them – and I think that’s great!! Everyone’s brain works different.
Sarah, I’ve mentioned this before, but I know I have folks just joining me – for rebates – there is a column and that’s where I enter them. I also have a tab called Rebate Tracker if you want to use it. Since Register Rewards/Catalinas are essentially coupons and processed as such, I simply include those deductions WHEN THEY ARE REDEEMED. For instance, today I bought the Joint Juice at Walgreens. I used two other Register Rewards in that transaction, so I saved $9. I would put that $9 in the Coupons column. I *might* note in the comments section that I earned a $10 Register Reward from that deal, but I won’t calculate it until I actually redeem it in a transaction. This seemed the simplest way possible to handle these tricky guys. Feel free to modify it in a way that makes sense for you, though. Just make sure you’re calculating the savings ONCE – either when you receive the RR or when you redeem it – and not both. I hope that makes sense!! Let me know if not.
When you enter the rebate in the rebate column it brings your OOP price down- is that how you keep yours or do you add it to the total value in the first column again so your OOP prices reflect what you actually paid?
Hi, Thanks for your tracker. I downloaded the new version. When I copy and paste my data in, it doesn’t fill in the “at a glance” section, so I copied and pasted my data from my old version into the new version January at a glance. Under Discounts, Savings, and Variance on the Summary Tab, it says #VALUE!. I am very familiar with Excel and can’t figure out what the error is. If I just try to do =D4-D3 on a cell off to the side on the January tab, I still get the value error. Any ideas? Thanks!
That at-a-glance section contained formulas and nothing needed to be entered in there. If you copied and pasted over, it likely produced errors in the formulas in the Summary tab.