Please help an MS Excel challenged person!

[Gabi]

Aaaaaaaa! I am almost ready to give up!

How in the world can you make a Balance column in a MS Excel file that calculates automatically and keeps a balance of whatever was added or subtracted from a column?

Example:


Item | Price | Balance
Sold Good Guitar | 20.00 | 20.00
Bought Donuts | -15.00 | 5.00
Sold old Chevy | 30.00 | 35.00

Now how do I make that Balance column to be updated automatically with each transaction I enter?

Please have mercy on a MS Excel challenged person like me...

thanks,
gabi.

[ab3640au]

Hi Gabi .. you've got to create a formula in the balance field (cell). To do this, you need to do the following:
1. Click balance cell with mouse and enter - =sum(
2. Drag (highlight) with mouse the cells above that you want calculated.
3. Close the bracket.
You'll end up with something like this - =sum(B3:B17), which is an equation which indicates that the cell should calculate the sum of cells B3 through to B17. Hope this helps.

Barry M

[EBE]

An easy way to do this:

Assuming cell b1 = 20
cell b2 = -15
cell b3 = 30

in c1 just move over the 20
in c2 put in =sum($B$1:B2)

now copy the cell function/value from c2 and paste into all the fields below C2.

done.....

Norm

[Newt]

You have to use an absolute reference in your formula. If you have columns A,B, C and rows 1-4(Titles in 1st row) in your spreadsheet above:

In C2 your formula should be =sum(B$2:B2)

Then copy that formula down in column C. It will always retain B2 as the start of the summed range, but change the second part to B3, B4, etc.

[Gabi]

guys... thanks a lot!...

I understood the movements... the only thing that I have the actual formula text "SUM($B$1:B2)" in the cell as opposed to the calculated sum...

gabi.

[EBE]

guys... thanks a lot!...

I understood the movements... the only thing that I have the actual formula text "SUM($B$1:B2)" in the cell as opposed to the calculated sum...

gabi.

you have to put in the = symbol for the function to work

Norm

[Gabi]

Wahooo!!!

I got it!!!!

But do I have to paste the same formula each time?
Is there a way I can apply a formula to the whole BALANCE column?

gabi...

[EBE]

Wahooo!!!

I got it!!!!

But do I have to paste the same formula each time?
Is there a way I can apply a formula to the whole BALANCE column?

gabi...

Just copy one of the cells with the formula in it. ( control-c is the copy short cut ). Then highlight (mouse left-click/drag as many cells as you want the formula in. Then paste ( control-v is the paste short-cut ). Since you use an absolute reference on the first cell ( example $B$2 ) and a relative reference on the second cell ( example B3 ) the cells will all update on the paste relative to their position.

Norm

[Gabi]

Awsome, Norm!

it works!!!

many thanks!
gabi.