I’m getting weird values like “2.27373675443232E-13” in my rollover adjustment column which makes the dashboard act like there is actually an adjustment needed but there isn’t. What data from the sheet(s) do you need to reproduce the issue?
I also have a -0.13 auto calculated adjustment on the latest budget, still trying to track down where that one is from as well. Any tips are welcome.
Hi @jonathan,
Thanks for adding this bug report. There is a known issue with this behavior, and @randy knows more about it than I do. He should chime in here soon.
Thanks for your patience.
Heather
Hey, @Jonathan! Sorry to hear the envelope budgeting solution is misbehaving.
Are you referring to the Rollover Adjustment message right under the “All transactions have been categorized this period.” message on the Dashboard?
Does your formula in cell D6 look like this?
=if(round(AE16)=0,"","For budget accuracy, redistribute "&text(abs(AE16),"$#,###,##0")&" of "&if(AE16<0,"un","")&"favorable Rollover Adjustment.")
If not, try “Add-ons/Tiller/Budget/Restore Dashboard” to get the latest version.
Hope this helps.
Randy
I just did a “Restore Dashboard” and the formula in D6 is still
=IF(AA8=0,"All transactions have been categorized this period.","For budget accuracy, please categorize "&IF(AA8=0,"zero transaction",AA8&" transaction")&if(AA8=1,"","s")&" this period.")
I think the root of the issue is that the calculation setting column E in the Budgets History sheet isn’t rounding the value it stores there and it should. Even rounding to 6 or 8 decimal places would still round to zero when the error is 12 places out which is what I’m seeing.
Sorry, @Jonathan, I misspoke… I meant the formula in D7. Could you compare D7 to the formula I posted? I hear you about rounding down. I guess it’s just a fussy floating-point arithmetic eccentricity. You can see in the D7 formula that we are performing the rounding as a we determine if the Rollover Adjustment is needed.
In your screenshot, it looks like the Rollover Adjustment message is gone so perhaps the update did the trick.
Randy
D7 isn’t where I’m having the issue. If I go into “Budget History” and delete any values in Rollover Adjustment and then run “Tiller -> Budget -> Analyze Budgets History” it creates tiny adjustments e.g. 6.82121026329696E-13 If I then go to the dashboard it shows “with Rollover Adjustment” in F11. It seems to me that the Analyze Budgets History script needs to round the “Rollover Adjustment” values before adding them to the Budgets History sheet.
I see what you are saying, @Jonathan. I’ve put in an enhancement request to implement the rounding on the add-on side, but that update may be a ways out. In the interim, rounding the Rollover Adjustment in-cell is a viable stopgap.
For example, this formula in F11:
="Total Rollover savings"&if(AE16=0,"","(with Rollover Adjustment)")
Becomes:
="Total Rollover savings"&if(round(AE16)=0,"","(with Rollover Adjustment)")
I’ve updated the master with this change if you want to do a Restore Dashboard or you can just make the change yourself directly in the sheet.
Good luck,
Randy
