Oct 23 2019 05:59 PM
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.
Oct 23 2019 06:57 PM
SolutionI@PAW918 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:
Oct 23 2019 06:59 PM
Oct 23 2019 07:06 PM
@mathetes Thank you so very much!! I truly appreciate your assistance! This is a big help!!!!!
Oct 23 2019 06:57 PM
SolutionI@PAW918 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: