Formula Help with Excel

[bethmsu96]

My spreadsheet has chronological info on rows 1-63 then starting on rows 66 and down is the same info but by agent. So I am running a sum up top with chronological data using =SUM(INDIRECT("E3:E"&ROW()-1))

but when I use that formula say on rows 69-75 for a particular agent

=SUM(INDIRECT("F69:F"&ROW()-1))

and I insert more rows up in the chronological section 1-63 to include say rows 64 and 65 now. It keeps throwing off all my formulas on the bottom half of the spreadsheet by 2 rows. Please help

[#2]

@bethmsu96 

You could use

=SUM(E69:OFFSET(E76,-1,0))

and

=SUM(F69:OFFSET(F76,-1,0))

[#3]

@bethmsu96 

It's better to use

=SUM(E3:INDEX(E:E,ROW()-1))
and
=SUM(E69:INDEX(E:E,ROW()-1))

With inserted row formula will be adjusted automatically

[#4]

@Sergei Baklan 

I used this formula and it worked great, thank you so much!!!!

=SUM(E69:INDEX(E:E,ROW()-1))

What was the difference between your formula with the INDEX and my formula with the INDIRECTor even OFFSET

=SUM(INDIRECT("E69:E"&ROW()-1))

Someone else even suggested =SUM(F69:OFFSET(F76,-1,0))

[#5]

@bethmsu96 

Both OFFSET and INDEX as well as some other functions in Excel return reference, thus work exactly the same way. For example, you may use

=IF(ISTEXT(A1),A2,A1):A10

which returns A2:A10 if value in A1 is text and A1:A10 otherwise.

The difference between OFFSET and INDEX is in behaviour - OFFSET is so called Volatile function which is recalculating on every change in worksheet. Other functions are recalculating if only dependant cells are changed. Thus on relatively big workbooks volatile functions could affect performance and better to avoid them. However, it depends. If you are more comfortable with OFFSET and workbook is relatively small - use it rather than save few milliseconds on recalculations. Better to save your own time.

INDIRECT takes hardcoded texts and convert it to reference. It's also volatile function (thus performance) and text used as an argument will be kept unchanged with adding/removing rows or any other changes in structure. Of course, you may generate the text based on some conditions which complicates the logic, but what for? Use INDIRECT if only no other options exist.