Sep 25 2020 12:47 PM
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
Sep 25 2020 01:04 PM
Sep 25 2020 01:05 PM
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
Sep 25 2020 02:25 PM
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))
Sep 26 2020 05:30 AM
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.