I need help in determining the Future Low Balance and Next Deposit Date in a Check Register.
Explanation of attached spreadsheet
Col A – Date that the item is to be processed, these may NOT be in date order
Col B – Amount of transactions
Col C thru E – for notes concerning transactions
Col F – Amount Processed as of Todays Date (G!)
Col G – blank
Col H – Current Balance of items processed as shown in Col F
Col I – In reality should be Cell I4 — This will be the Smallest Balance from Today to the End of the Year.
Col J – Theoretically what the balance will be if the items are processed as entered.
Col K – In Reality should be Cell K4 – the date of the nest Deposit from Today to the End of the Year.
Input is limited to Colomn A and B, Date of transaction and amount.
It was suggested that I use MINIFS but I can not make it work properly. My thoughts, that do not work, are …
Cell i4 … Lookup(smallest in Col K in the range equal to todays date and A374 … …
Cell K4 … Lookup(Nest #>0 in Col K in the range equal to todays date and A374) … …
It is being done in a Google docs spreadsheet as follows but that is not working in Libre …
Cell I4 … =query(sort({A4:A,sumif(A4:A,"<="&A4:A,B4:B)},1,true),“select Col2, Col1 where Col1 >= date '” & text(G1,“yyyy-mm-dd”)&"’ order by Col2 asc, Col1 asc limit 1") … …
Cell K4 … =min(filter(A4:A,A4:A>G1,left(C4:C)=“d”))
Any assistance will be appreciated.