Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

SU513407 example

share|improve this question
add comment

2 Answers

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.

SU513407 example

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
1  
@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
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.