End of range changes when I add a number

[Julian]

Please could someone explain why Excel 2000 changes the range in
another cell when I type a number into a blank cell?

I have a spreadsheet that calculates income by month, something like
this:

Month Income1 Income2 Income3 Cost Total
Jan06 23.99 44.77 55.00 123.76

The Total cells contain a formula =SUM(B22)-E2 (actually there are
more income columns, but this is the principle.)

I copied this Total formula in the first row, and pasted it into the
other 11 rows for the other months.

Originally there was no cost column. I inserted this column, and then
modified the formulas as shown above.

What is driving me crazy is that when I type a number into one of the
cost cells, the formula in the Total cell for that row changes itself
to SUM(Bx:Ex)-Ex , so that the result is the same, the cost is added in
and then subtracted.

Why is it doing this, and how can I stop it?

Julian

 

[Pete_UK]

When you inserted the column, Excel might have extended the formula for
you. Just ensure that you have the correct formula in F2, then
double-click the fill handle (the small black square in the bottom
right corner of the cursor) to copy the formula down over the existing
cell contents.

Hope this helps.

Pete

 

[Julian]

HI Pete.

It did change the formula when I inserted the column (originally it
*was* just a sum.) I changed the formula so that the new column was
subtracted from the sum of the others, and copied it down as you
suggested. But then I found that Excel was changing the end cell of the
range whenever I typed a number into ther new column.

In fact, it does this only for the third and lower rows (e.g. March
onwards.) I have tried copying the formula as you said, I have copied
and pasted it into each cell individually, and I have manually edited
the formula in each cell. Whatever I do, Excel always changes it back!!

Julian