Hi everyone,
I'll try to explain this as best as I can, but forgive me if I am not clear enough! I'm so sorry this is lengthy, but I need to give you some context to try and make sure it is understandable!
Please someone take the time to read! Please lol. Thank you so much.
My spreadsheet tracks personal finances. On the homepage (which I've called the dashboard), there are a number of accounts going in columns left to right and dates from Jan 01 to Dec 31 2024 in column A, all the way down (364 entries). There is then a balance of that account, on that day, in the cell where the X and Y axis meet. I have a separate tab for recording transactions over the year. This transactions page has various columns for recording whether it was money in or out, what account (of the 7 on the dashboard) is the in or out to/from and what the expense was for (fuel, medical costs etc).
This transactions tab also has a column called 'Report', in which the user can, for each transaction, mark Y or N in the box. This Y or N marks whether or not the transaction should be recorded on a profit & loss report (which is on another tab). This P&L just has all of the income transactions (In) LESS all of the expense transactions (Out), providing the letter at the end of the transaction lines is 'Y'. Hope you're still with me...
Each of the account columns on the dashboard just has a sumif formula in them, to add or takeaway any transactions that for example, equal the same date as in column A on the dashboard, have the same account that the column refers to etc. This way all transactions can be input onto the transactions tab, and the dashboard tab will keep count for the balances of each account. This aids in future forecasting as transactions can be added and you can see in the future what the balances will be. So you can put in the transactions tab something like:
Column A Column B Column C Column D Column E
Date In Account 1 500.00 Y
on the dashboard it would have account 1 add 500.00 to it's total on that day.
Some transactions have already been put into the transactions list as these are the opening balances for each of the accounts (as they each will have a little money in at the start of the year). These entries have been marked as 'N', as opening balances wouldn't/shouldn't have any effect on actual NEW money In and Out over the year. The opening balances need to be ignored.
Therefore, I have a cell at the top of the dashboard that shows sum of the balances for each account at the end of year (account 1 balance at 31/12/24 + account 2 balance at 31/12/24 etc) and another cell which shows the profit and loss net figure (income - expenses). You would expect the different between these two to therefore be the total of all of the opening balances. The opening balances would be included in the account balances but not in the profit and loss report. Make sense?
Okay so here's the problem...
The end of year balances total currently is: 6,194.23 and the profit and loss net is: 6,000.99. That is a 193.24 difference, which is the total of ALL of the opening balances. So far so good.
One of the opening balances is 159.79. Now lets say that this opening balance changes, we're gonna have more money at the start of year than thought, 5.77 more in fact. Now, my thoughts are that if you change that opening balance figure to 165.56 (159.79 + 5.77), the end of year balances total would be 5.77 more (at 6,200), the profit & loss net would stay the same (as opening balances are excluded with the "N"), so the difference would still be the total of all of the opening balances (which in this case would be 199.11).
The end of year total balances is 6,200.23, rather than just 6,200.00 (which is what it should be if you are adding only 5.77). The p&l net is remains the same at 6,000.99. The difference between them is 199.24 and not 199.11. I don't know why but it seems that 5.77 is being rounded to 6, because if you look at the balance on the accounts before and after adding that 5.77, there is a 6 difference.
I have tried everything from changing the cell formats to stop any rounding, made sure there aren't any round functions in the formulas, extended the numbers with the 00.0>> button to make sure they aren't somehow different, but nothing works. That 5.77 is being added to opening balance, increasing the balance on that account by 5.77 but it's working it out at 6! If I change the end of year balances figure at the top of the dashboard from an auto calculated one (coming out at 6,200.23) to 6,200.00, it works! It is for some reason taking that 5.77 extra and converting it to 6 and therefore adding on 6.
Please can someone take the time to read through this and come up with any ideas of what I could do?!
Thank you so much for taking the time to read this! I am so so appreciative.
Love to everyone!
