End of range changes when I add a number

  • Thread starter Thread starter Julian
  • Start date Start date
J

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(B2:D2)-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
 
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
 
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
 
Back
Top