Microsoft excel formulation unstable

  JohnM. 05:50 01 Dec 03
Locked

I have a large spreadsheet with 30 columns and over 700 lines. One section has to be added up down 6 columns. I am making constant alterations by adding new lines. Problem this seems to affect the addition formula which is always losing the top range of the columns. ie x2:x700 becomes x25:x700 how do I stop this.

JohnM.

  VoG II 06:35 01 Dec 03

Use absolute references.

X$2:X3

and copy down.

  VoG II 12:47 01 Dec 03

Sorry, that's not entirely clear is it!

Say your formula at present is

=SUM(X2:X200)

change it to

=SUM(X$2:X200)

Then when you add extra rows the summation will still start with row 2.

A little tip when inserting rows in sum formulas is to always insert the new row above the formula row OR at row 2 of the range.

For example -

If you had a range of data from A1 to A9 and in A10 you had the formula =sum(A1:A9) then to insert a new row you would either click on the number 2 or 10, this way the formulas integrity would be maintained.

If it is important that the data that is in the new row should be at the top of the data block then modify the above by having a blank row at row 1, data in rows 2 to 9 and the formula in row 10 BUT the formula includes the blank row and the top insertion is still done on Row 2.

As VoG points out Absolute references should be used wherever practical, they will automatically update as you add in rows.

HTH

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

These are the Best Christmas Ads and Studio Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…