Forum Discussion
Payment Tracker Calculator
Hi! I am trying to create a spreadsheet in excel to subtract payments and give an ending balance, but unsure of what formula to use or how to input. I want it to be set up to automatically calculate without me having to enter the formula in each cell, but I'm unable to do so. Here's a screenshot for reference. Any assistance would be greatly appreciated.
IPAW918 I've attached a sheet that does those calculations. By the way, your screen image would suggest that you entered the first formula incorrectly; it looks as if you subtracted the 14,237.50 from the 300, rather than the other way around.
The formula I wrote for you keeps the "Balance" column blank until a number is entered under "Payments" and does so by using an IF statement. So it's more than you asked for, but helps make the page look neater and easier to understand.
In the sample I've attached, I have the balance in Column C, so from C3 on down the formula subtracts the payment amount from the current row from the balance in Column C in the row above.
=C2-B3 is the simplest formula; so IN cell C3, it says subtract B3 (the payment) from C2 (the prior balance)
My formula, so it stays blank until you enter a payment, is =IF(B3="","",C2-B3)
Translated into English, IF the payment cell is blank, enter a blank in C3, otherwise subtract payment from C2.
Attached is the sample as a working sheet. Here's a screen capture:
4 Replies
IPAW918 I've attached a sheet that does those calculations. By the way, your screen image would suggest that you entered the first formula incorrectly; it looks as if you subtracted the 14,237.50 from the 300, rather than the other way around.
The formula I wrote for you keeps the "Balance" column blank until a number is entered under "Payments" and does so by using an IF statement. So it's more than you asked for, but helps make the page look neater and easier to understand.
In the sample I've attached, I have the balance in Column C, so from C3 on down the formula subtracts the payment amount from the current row from the balance in Column C in the row above.
=C2-B3 is the simplest formula; so IN cell C3, it says subtract B3 (the payment) from C2 (the prior balance)
My formula, so it stays blank until you enter a payment, is =IF(B3="","",C2-B3)
Translated into English, IF the payment cell is blank, enter a blank in C3, otherwise subtract payment from C2.
Attached is the sample as a working sheet. Here's a screen capture:
- P.S. Once you've got the formula, you just copy it down Column C (or whichever column you have "Balance" in. The sample I sent you has the formula down as far as row 20 (cell C20)
