Create a total formula with variable input above

Question

Excel 2003 – Statement Totals formula problem

Should be easy, but just can’t figure it out!

I have 3 columns – Debit, Credit, Balance. The Balance column holds a balance whenever I send out a statement (variable timing), which equals Previous Balance plus any Debits incurred since the previous balance less any Credits incurred since the previous balance. Simple.

I’d like to make this an automatic formula that can be included in a cut/paste of a Total row showing Balance Due. How can I translate this into a formula?

I can easily do this if I keep a running balance, but haven’t worked out how to do it otherwise… hope you can help.

Debit       Credit       Balance

      Balance Due          50.00

10.00     

 5.00

                10.00

       Balance Due         55.00

 2.00

 6.00

 8.00          

                 20.00

         Balance Due       51.00 - how to automate balance due with variable data above?

Answer 1

Yes, this works, and better, I can see the logic. But I was hoping to be able to tally from the prior balance rather than the entire history - forgot to include that. So, there's no absolute cell location to work from, only relative to the current balance cell. So, if I could work out how to use the current balance cell minus 1 until I hit the prior balance, then add it, and add any debit/credits between the two balance cells, I'd have it. Any joy?

An inelegant solution would be to use a hidden column for a running total, I suppose? But I'd far rather the former...

Thanks for your support!

Is the term "Balance Due" an actual cell entry?

I have a formula that will put the balance at every instance of a credit. Like this:

Debit	Credit	Bal
		50
10
5
	10	55
2
6
8
	20	51
10
10
10
2
3
	10	76
	10	66
	5	61
1
	10	52

Is that something you can use?

--

Biff

Microsoft Excel MVP

Answer 2

Thanks very much for your reply, but I'd rather not have a running total if I can avoid it.

Answer 3

Yes, this works, and better, I can see the logic. But I was hoping to be able to tally from the prior balance rather than the entire history - forgot to include that. So, there's no absolute cell location to work from, only relative to the current balance cell. So, if I could work out how to use the current balance cell minus 1 until I hit the prior balance, then add it, and add any debit/credits between the two balance cells, I'd have it. Any joy?

An inelegant solution would be to use a hidden column for a running total, I suppose? But I'd far rather the former...

Thanks for your support!

Answer 4

Hi,

lets use "Banalce" coulumn as a "Balance Due" and insert running total with "Date" parameter also.

A                                    B                 C               D

1 Date                           Debit           Credit     Balance Due

2 Balance Due                                                        50

3 21/01/2011                  10                             =D2+B3-C3  (i.e 60)

4 21/01/2011                    5                             =D3+B4-C4  (i.e 65)

5 22/01/2011                                      10         =D4+B5-C5  (i.e 55)

---

Hiren

Answer 5

Assuming that your initial balance of $50 is in C2 and that Debits are in column A, with $10 in A3, and credits in column B also potentially starting at row 3: try this formula on the Balance Due rows, try this formula where you now have 55 and 51:

=C$2+SUM(INDIRECT("A$3:A" & ROW()-1))-SUM(INDIRECT("B$3:B" & ROW()-1))

---

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein