Discussion Re: Help with Formula in Excel
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2343301#M99456
Thanks!Tue, 11 May 2021 06:04:03 GMTragomes19722021-05-11T06:04:03ZHelp with Formula
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299120#M97846
<P>Hi!</P><P>I have to calculate the average sprint velocity from a table where:</P><P>- Column A = Sprint # (1 through 28 where 28 is at the top)</P><P>- Column B = sprint velocity</P><P> </P><P>So based on the above table, the user will select the number of sprints he/she wants to go back for the average velocity calculation. Say the user selects 6 from the drop-down, then only the last 6 sprints' velocity should be used for the calculation of average velocity (sprints 28,27,26,25,24, and 23).</P><P> </P><P>As I already had this formula working on Google Sheets, I'm trying to convert it into Excel but haven't figured out a way to do so. Apparently, the formula ROW which returns a range can't be used with AVERAGEIF like Google Sheets does...</P><P> </P><P>Google Sheets formula:</P><P>=ARRAYFORMULA(AVERAGEIF(ROW(INDIRECT(Veloc_SPs))-3, <SPAN class=" string ">"<="</SPAN><SPAN class=" default-formula-text-color">&</SPAN><SPAN>F8</SPAN>,INDIRECT(Veloc_SPs))))</P><P> </P><P>Can you please assist based on sample spreadsheet attached?</P><P>Also, please notice that I have a cell with the velocity range from column B defined as a named Range so I can use that name in my formula. </P><P> </P><P>Thank you in advance!</P>Tue, 27 Apr 2021 18:30:26 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299120#M97846ragomes19722021-04-27T18:30:26ZRe: Help with Formula
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299229#M97850
<P><LI-USER uid="1024126"></LI-USER> </P>
<P>In N8:</P>
<P> </P>
<P>=AVERAGE(OFFSET($B$1,1,0,J8,1))</P>Tue, 27 Apr 2021 18:49:13 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299229#M97850Hans Vogelaar2021-04-27T18:49:13ZRe: Help with Formula
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299304#M97855
Thank you very much, <LI-USER uid="127945" login="Hans Vogelaar"></LI-USER>!<BR />What if my table with Columns A and B is defined as a Table in Excel, how would I reference to "$B$1" instead?<BR />I tried transferring the formula but because I actually have it defined as a table, it's not working. Sorry about that...Tue, 27 Apr 2021 19:14:02 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299304#M97855ragomes19722021-04-27T19:14:02ZRe: Help with Formula
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299430#M97858
<P><LI-USER uid="1024126"></LI-USER> </P>
<P>As variant</P>
<LI-CODE lang="excel">=AVERAGE(INDEX(Table1[Velocity],1):INDEX(Table1[Velocity],J8))</LI-CODE>Tue, 27 Apr 2021 19:32:32 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2299430#M97858Sergei Baklan2021-04-27T19:32:32ZRe: Help with Formula
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2343301#M99456
Thanks!Tue, 11 May 2021 06:04:03 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2343301#M99456ragomes19722021-05-11T06:04:03ZRe: Help with Formula
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2343302#M99457
Thank you!Tue, 11 May 2021 06:04:11 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2343302#M99457ragomes19722021-05-11T06:04:11ZRe: Help with Formula
https://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2343475#M99460
<P><LI-USER uid="1024126"></LI-USER> </P><P>In my opinion, working with row numbers is a distraction created by normal spreadsheet practice; the underlying problem rarely depends on the placement of the data on the worksheet. In this case, there is a more meaningful index 'sprints' to build upon. You know how many there are and how many you wish to use; problem solved!</P><LI-CODE lang="excel">= AVERAGEIFS(Velocity, Sprints,">"&(COUNT(Sprints)-usedSprints))</LI-CODE><P> Using range operations to identify the required data can provide a sound basis for solution building but, in this case, they are derived from the 'sprints' counter and I think simply using the counter offers a more direct approach.</P>Tue, 11 May 2021 07:11:00 GMThttps://techcommunity.microsoft.com/t5/excel/help-with-formula/m-p/2343475#M99460Peter Bartholomew2021-05-11T07:11:00Z