# Positive result, negative result and current balance. How do you make starting balance show current result?

I have 3 columns:

Column A shows positive result and if the result is negative then it is in a column B.
Column B shows negative result and if the result is positive then it is in a column A.

(meaning that either columns can have 0.00 in the cell (empty zero cells)).

Column C has starting assets and it also shows the current balance that while result A or B are adding up and current balance is showing the current result.

What is the proper formula for this?

-

Because (i) such information (eg Inventory issues and returns) is often not provided in separate columns (though yours is!) but as positive and negative values all in the one column and (ii) the movement itself can be useful information, an alternative solution might be of interest.

With data as provided by @Al Everett, this would mean starting by combining columns `A` and `B`, (as in `ColumnD` in the example below where the column references have been shunted to the right to leave room, as in `ColumnA`, for possibly more meaningful information for the transactions than that they happen to be on, say, your `Row4` - ie that these are for, say, `3/1/12`).

The movement (the net of your `A` and `B`) is simply `A-B`, or in the example, achieved with the formula:

``````=B2-C2
``````

in `D2` copied down as far as required.

In `E2` the formula:

``````=SUM(D\$2:D2)
``````

will give the ‘running total’ (cumulative sum) when copied down as far as required. Because `D2` is a relative reference it will automatically increase: D2>D3>D4 etc when copied down. `D\$2` however uses a fixed reference and will not change when the formula is copied down, meaning that the range to be summed will always start with the value in cell `D2`, though always end with the value in `ColumnD` for the respective row.

-
Thank you Pnuts for your input, I really appreciate it but I dont understand a single thing. It is too sophisticated. It will take me a while to understand what you wrote. I am a simple user for excel and I like to say and keep things simple. I wish you all well man. Thank you again. – Tine Dec 2 '12 at 10:46

It's hard to understand what you're after without some sample data. If I'm reading you right you want something that looks like this:

``````          A             B              C
1  starting value                   200.00
2       50.00          0.00         250.00
3        0.00        200.00          50.00
4      100.00         25.00         125.00
``````

If that's so, then the formula for column C is

``````=C(previousrow) + A(currentrow) - B(currentrow)
``````

Specifically, cell C2 above would be

``````=C1 + A2 - B2
``````

and so on down the sheet. (I assume you know how to do autofill.)

You've also said in the comments that you would like to suppress the result if both A and B are 0. pnuts offers this adjusted formula:

``````=IF(AND(A2=0,B2=0),0,C1+A2-B2)
``````

This just says: If A2 and B2 are both equal to 0, display a 0. Otherwise, calculate C1 + A2 - B2.

-
Hi Al! Thank you so much for your answer. It has never occurred to me that it can be that simple,hehe. What have I been thinking. It works! Unfortunately I can not upload an image which sucks.The criteria for me being as a new user is unfair. Anyway...What you displayed is exactly what I wanted. The thing that happens in Column C now is...even if I have A=0 and B=0, C keeps showing the last price. How do you do the formula when you want C to show 0 if A or B are of 0 value. – Tine Dec 2 '12 at 10:40
Sorry for my crunched text. This text window is not working as it appears when writing a comment. – Tine Dec 2 '12 at 10:41
@Tine "price" and C=0 if OR(A,B)=0 seems a strange requirement so your question may be being misread. You are not (yet!) allowed to post images here (to reduce abuse) but are permitted to post a link to an image. Can you do that? PS Thanks for your other comment! – pnuts Dec 2 '12 at 10:54
@pnuts ... postimage.org/image/65e96eju3 I know but it is silly. I am not here to abuse,hehe. Have no time for that, nor I would take any. I guess it is what it is. :) You are most welcome. Any help I can get, I appreciate it. Hopefully picture is helpful. – Tine Dec 2 '12 at 12:47
@Tine: Also, the way to express gratitude on Stack Exchange sites is to vote up the questions that helped you. If a particular question helped you the most, you should "accept" it by clicking the hollow checkmark next to the answer. – Al E. Dec 3 '12 at 13:25