I have a spreadsheet where I record in goings and outgoings between 2 accounts for my personal budget
Once a fortnight when I get paid I do a transfer to my credit card but must leave a minimum amount in the cheque account we are transferring from to cover other direct debits in the fortnight ahead.
The formula I would like would calculate how much we would need to transfer to bring the credit card back to it's limit if any (eg 6,000) but without going over the minimum amount needed in the other account to cover direct debits (eg 1000)
So lets say we need to transfer 5000 to the credit card to pay it off, but there is only 5000 in the other account how do I calculate it so it knows yes we do have to transfer some money but only take 4000 even though that means the credit card balance is not paid in full, as well as knowing that if the general account had 20,000 in it to only take 5000 so the credit card isn't over paid as well as knowing if the balance is at the limit of 6,000 not to transfer any.
I have tried a nested if statement but was getting stuck on circular references.
The columns on the spreadsheet are income, expenditure, running total for each account.
Hopefully that makes sense
