Discussion Re: Formula Help with Excel in Excel
https://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714830#M76126
<P><LI-USER uid="521"></LI-USER> </P><P>I used this formula and it worked great, thank you so much!!!!</P><P>=SUM(E69:INDEX(E:E,ROW()-1))</P><P> </P><P>What was the difference between your formula with the INDEX and my formula with the INDIRECTor even OFFSET</P><P>=SUM(INDIRECT("E69:E"&ROW()-1))</P><P> </P><P>Someone else even suggested =SUM(F69:OFFSET(F76,-1,0))</P>Fri, 25 Sep 2020 21:25:35 GMTbethmsu962020-09-25T21:25:35ZFormula Help with Excel
https://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714474#M76113
<P><SPAN>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)) </SPAN></P><P><SPAN>but when I use that formula say on rows 69-75 for a particular agent </SPAN></P><P><SPAN>=SUM(INDIRECT("F69:F"&ROW()-1)) </SPAN></P><P><SPAN>a</SPAN><SPAN>nd 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</SPAN></P>Fri, 25 Sep 2020 19:47:48 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714474#M76113bethmsu962020-09-25T19:47:48ZRe: Formula Help with Excel
https://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714582#M76118
<P><LI-USER uid="809582"></LI-USER> </P>
<P>You could use</P>
<P> </P>
<P>=SUM(E69:OFFSET(E76,-1,0))</P>
<P>and</P>
<P>=SUM(F69:OFFSET(F76,-1,0))</P>Fri, 25 Sep 2020 20:04:15 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714582#M76118Hans Vogelaar2020-09-25T20:04:15ZRe: Formula Help with Excel
https://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714583#M76119
<P><LI-USER uid="809582"></LI-USER> </P>
<P>It's better to use</P>
<LI-CODE lang="excel">=SUM(E3:INDEX(E:E,ROW()-1))
and
=SUM(E69:INDEX(E:E,ROW()-1))</LI-CODE>
<P>With inserted row formula will be adjusted automatically</P>Fri, 25 Sep 2020 20:05:11 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714583#M76119Sergei Baklan2020-09-25T20:05:11ZRe: Formula Help with Excel
https://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714830#M76126
<P><LI-USER uid="521"></LI-USER> </P><P>I used this formula and it worked great, thank you so much!!!!</P><P>=SUM(E69:INDEX(E:E,ROW()-1))</P><P> </P><P>What was the difference between your formula with the INDEX and my formula with the INDIRECTor even OFFSET</P><P>=SUM(INDIRECT("E69:E"&ROW()-1))</P><P> </P><P>Someone else even suggested =SUM(F69:OFFSET(F76,-1,0))</P>Fri, 25 Sep 2020 21:25:35 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1714830#M76126bethmsu962020-09-25T21:25:35ZRe: Formula Help with Excel
https://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1715985#M76163
<P><LI-USER uid="809582"></LI-USER> </P>
<P>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</P>
<LI-CODE lang="excel">=IF(ISTEXT(A1),A2,A1):A10</LI-CODE>
<P>which returns A2:A10 if value in A1 is text and A1:A10 otherwise.</P>
<P> </P>
<P>The difference between OFFSET and INDEX is in behaviour - OFFSET is so called <A href="https://exceljet.net/glossary/volatile-function" target="_self">Volatile function</A> 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.</P>
<P> </P>
<P>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.</P>Sat, 26 Sep 2020 12:30:03 GMThttps://techcommunity.microsoft.com/t5/excel/formula-help-with-excel/m-p/1715985#M76163Sergei Baklan2020-09-26T12:30:03Z