Forum Discussion
Formula Help with Excel
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
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))
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):A10which 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.