Forum Discussion
Formula to calculate between "Closing Balances"
Hi,
I have a sheet which uses formula to calculate balance for repayments and draw for a facility.
Each period can have a different number of draws & repayments, but i wish to have a summary balances for the end of each period.
Is there a formula i can add to each column to use one formula during the period, then use a sum formula between the rows for the period? Attached is an example.
I believe is is a IF formula, but is the one to use something like term "Closing Balance" as the reference on where to calculate between?
Thanks for any assistance.
Kind regards,
2 Replies
=IF(B2="Closing Balance",SUMIF(A:A,A1,P:P),IF(AND(N2=0,B2="Beginning Balance"),O2*(L2+M2)*(DAYS(K2,J2)+1)/365,N2*(L2+M2)*(DAYS(K2,J2)+1)/365))This formula can be copied down across all rows.
Cheers,
--
LR
See attached. I have entered this formula in I6, and then copy/paste it to P6, I9 and P9. You can the same for every following period.
=SUMIF($A:$A,$A5,I:I)Personally, I would favour an approach where you just list all draws and repayments in one continuous list and than insert sub-totals (Data / Subtotal), whenever you needed/desired. This can be done with a few mouse-clicks and does not mess-up your data or formulae. This is demonstrated in the second sheet. Here, I also simplified some of your formulae, by eliminating most of the IF functions.
