Excel formula help - add and subtract

  Forum Editor 10:19 09 May 15
Locked
Answered

I readily admit that when it comes to Excel formulae I am a complete idiot - it's something that I have rarely had to deal with (that's my excuse anyway).

Now I have a simple job to do, and i need help.

I have a simple cash reconciliation workbook, and I need a formula that will do this:-

Whenever I enter a cash amount in a cell in column B I want it to be added to a running total in the same row in column E

I want any cash amount entered in a cell column C to be deducted from the running total in the same row in column E

I'm sure that there are people here who can provide me with the answer in ten seconds flat. I would be very grateful.

  AroundAgain 11:55 09 May 15

FE

Seeing as the 'intelligent' guys are seemingly not around just yet, this might get you started.

It won't let me post an image, so I'll try to write it.

Keeping Cell D4 blank, In Cell D5 type (without the quote marks) - "=SUM($D$4+B5-C5)" In Cell B5, type your credit amount In Cell C5, type your debit amount

You should see the total appear in Cell D5

Once you get this far, go to the ' Total' cell (D4 in my case), place your cursor on the lower right corner of that cell, moving around slightly until you get a thin + sign. Then 'click and drag' down several cells - this will copy the formula down, automatically changing the relevant cell references.

Unfortunatly, I can't remember how to add to the formula so that you don't get all the '0' showing. I'll work on it but I suspect somebody, much more knowledgeable than I am, will come up with a solution.

So, maybe for now, this will help

  bumpkin 12:24 09 May 15

Not done this for years but from memory there is a template under "general templates" that should do what you want.

  bumpkin 12:26 09 May 15

Just tried it and in my case it required the disk which I can't find.

  lotvic 12:33 09 May 15

Q. is the cash amount entered in a cell in column C a debit or credit amount? (eg: is it entered as -6.73 or as 6.73?) if it is typed/entered as a minus -6.73 then you have to add the cell in your formulae, +C5 not -C5

  lotvic 12:50 09 May 15

To be a little clearer:

if C cell is typed as a minus -6.73 then in E5 type =SUM(E4+B5+C5)

if C cell is typed as credit 6.73 then in E5 type =SUM(E4+B5-C5)

and as Around Again says, drag the thin + in the corner to copy the formulae down to next rows

  Forum Editor 13:30 09 May 15

lotvic

column B contains amounts billed to clients. Column C contains amounts received. Column E contains total balance outstanding, not between a single bill or payment, but between all bills and all payments. Payments will not necessarily relate to single bills, but to a batch of them. Other columns contain different, irrelevant text notes.

When a bill is raised it is entered in a cell in column B, and I want that entry to be added to the running balance in column E. When a payment is received it is entered in column C and the total outstanding is reduced. Each row therefore contains a billing amount or a payment, plus a balance.

  bumpkin 15:06 09 May 15

Start at E3 and enter =Sum(E2+B3-C3) then copy the formula down the page.

  lotvic 15:44 09 May 15

"Column C contains amounts received"

Ok, but when you type the amount received, are you typing the minus sign then the amount? eg: -6.73

or are you just typing the amount? eg: 6.73

this will dictate whether the formula in Column E contains +C5 or -C5

  lotvic 15:55 09 May 15
Answer

Here's a link with examples ClickHere as can be deduced in order to use the more advanced Autosum which adds a range of cells you need to have the correct minus - entry in a cell if you want that cell to subtracted instead of added. This doesn't matter so much if as in this case you're only doing column B and C but it gets messy if you have multiple columns, for instance entries in B, C, D, E, F, and your running total in column G.

  Forum Editor 16:23 09 May 15

lotvic

I'm not typing any minus signs. I want to have a date in A, an amount charged in B, an amount paid in C, a date in D, and a running total in C.

Each time I make an entry in B the running total in E should increase, and when I make an entry in C it should decrease.

This thread is now locked and can not be replied to.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch