Balance Column for Cost, Refund and Deposit

[pjangie]

I have a very basic expense sheet that I cannot figure out how to calculate the total balance column/cells properly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Client Name[/TD]
[TD]Item Description[/TD]
[TD]Cost[/TD]
[TD]Refund[/TD]
[TD]Deposit[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]3/29/19[/TD]
[TD]Admin[/TD]
[TD]Call Deposit[/TD]
[TD][/TD]
[TD][/TD]
[TD]$300[/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD]4/1/19[/TD]
[TD]Jones[/TD]
[TD]Call Cost[/TD]
[TD]$86[/TD]
[TD][/TD]
[TD][/TD]
[TD]$214[/TD]
[/TR]
[TR]
[TD]4/2/19[/TD]
[TD]Jones[/TD]
[TD]Call Refund[/TD]
[TD][/TD]
[TD]$86[/TD]
[TD][/TD]
[TD]$300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need the balance column to calculate to either add or subtract the Cost/Refund/Deposit amounts. One balance cell should have a formula to let it know to add or subtract depending on which amount is added to the other columns/cells (Cost column will be a subtraction while Refund and Deposit are additions). Make sense? Please help. Thanks!

 

[DanteAmor]

Try

<b></b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:116px;" /><col style="width:116px;" /><col style="width:116px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Date</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Client Name</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Item Description</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Cost</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Refund</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Deposit</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Balance</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">3/29/19</td><td >Admin</td><td >Call Deposit</td><td > </td><td > </td><td style="text-align:right; ">$300</td><td style="text-align:right; ">$300</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">04/01/2019</td><td >Jones</td><td >Call Cost</td><td style="text-align:right; ">$86</td><td > </td><td > </td><td style="text-align:right; ">$214</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">04/02/2019</td><td >Jones</td><td >Call Refund</td><td > </td><td style="text-align:right; ">$86</td><td > </td><td style="text-align:right; ">$300</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=IF(ISNUMBER(G1),G1,0)+SUM(E2:F2)-D2</td></tr></table></td></tr></table> <br /><br />

 

[mumps]

In cell G2: =E2+F2-D2
In cell G3: =G2+E3-D3+F3 and copy down the column

 

[pjangie]

Thank you! Another question - how do I get the copied cells to show either $0 balance or blank until something has been entered in that row. Right now each of the cells below are reflecting the amount above. Thank you!!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G
1	Date	Client Name	Item Description	Cost	Refund	Deposit	Balance
2	3/29/19	Admin	Call Deposit			300	300
3	########	Jones	Call Cost	86			214
4	########	Jones	Call Refund		86		300
5
Report

Cell Formulas
Range	Formula
G2	=IF(COUNT(D2:F2)>0,N(G1)+SUM(E2:F2)-D2,"")

 

[mumps]

In cell G2: =IF(AND(D2="",E2="",F2=""),"",E2+F2-D2)
In cell G3: =IF(AND(D3="",E3="",F3=""),"",G2+E3-D3+F3) and copy down the column

 

[DanteAmor]

Thank you! Another question - how do I get the copied cells to show either $0 balance or blank until something has been entered in that row. Right now each of the cells below are reflecting the amount above. Thank you!!

You're Welcome.

I understand that to save time you decide to copy the formula down, but it is not advisable to use formulas if you are not occupying them, that is, in this case, there is no data to add, so do not copy the formula; it takes up space and memory in your file.
Either way, here is my solution.

=IF(A2="","",IF(ISNUMBER(G1),G1,0)+SUM(E2:F2)-D2)