Excel 2000 - Absolute formulas not working as desired

[MrsTFB]

I have a row of information that I insert a line into at the top and add information. My formula is : =SUM($C$4:C10)/SUM($B$4:B10) I put the absolutes in because I want the beginning cell for calculation to ALWAYS be cell 4. But, when I insert a row, or cells it still moves to 5. I thought the absolutes would fix this. What will I have to do to make the beginning data ALWAYS be C and B 4?

It seemed simple, but it's not.

Thanks,
MrsTFB in Tennessee

 

[GlennUK]

That's not the way references work. If you are inserting a row before a range reference the entire range reference will adjust downwards by one row. When you insert a row inside a range reference then the range reference will expand by a row ... as the insertion was done within the range. You can either insert a dummy row and have the SUMs reference that as the beginning of the range ( and do your insertions after that row ), or use the INDIRECT function to generate the desired results. As example looks like this:

=SUM(INDIRECT("$C$4"):C10)/SUM(INDIRECT("$B$4"):B10)


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[cheerio]

It's not only rows that cause a question. Insert a new column B so that the old C becomes a D and you would have a change in reference.

If you can guarantee that cell A1 will not be disturbed (eg if it holds your report name) you could use OFFSET instead of INDIRECT.

For example:

=SUM($C$4:C10)

becomes

=SUM(OFFSET(A1,3,2):C20)

Here the offset says move 3 rows down from A1 and 2 columns to the right which puts you on $C$4 while A1 stays fixed.

However, if you should insert a column B your range changes to

=SUM(OFFSET(A1,3,2)20)

so you now have two columns in the SUM range.

You need to be clear exactly what you are trying to cover.

 

[SilentAiche]

Just to expand slightly on Glenn's post (which I am so not quailified to do!!): he mentioned inserting a row before a range and how it would not be included within the range. All I wanted to add was that you will have the same problem below the range, so be careful.

Example (tested on Excel 2002 on Win XP): you have figures in B1, B2 and B3, and =sum(b1:b3) in cell B4. If you make B4 the active cell and click Insert/Row, the new row will not be included in the total. The formula, now in B5, will remain =sum(b1:b3) regardless of whether the cell references are absolute or not.

BTW, the dummy row method has worked well for me.

THanks,
Tim

 

[MrsTFB]

I'm going to use the dummy row. THat will always start my formula at c3 and the last row that does increment will be fine. I thought there would be a formula or something for this.

Thanks for all your help. As long as I get the results I want, I'm happy.

MrsTFB in Tennessee