 SOLVED

# Help with Formula

Hi!

I have to calculate the average sprint velocity from a table where:

- Column A = Sprint # (1 through 28 where 28 is at the top)

- Column B = sprint velocity

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).

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...

=ARRAYFORMULA(AVERAGEIF(ROW(INDIRECT(Veloc_SPs))-3, "<="&F8,INDIRECT(Veloc_SPs))))

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.

6 Replies
best response confirmed by ragomes1972 (Occasional Contributor)
Solution

# Re: Help with Formula

In N8:

=AVERAGE(OFFSET(\$B\$1,1,0,J8,1))

# Re: Help with Formula

Thank you very much, @Hans Vogelaar!
What if my table with Columns A and B is defined as a Table in Excel, how would I reference to "\$B\$1" instead?
I tried transferring the formula but because I actually have it defined as a table, it's not working. Sorry about that...

# Re: Help with Formula

As variant

``=AVERAGE(INDEX(Table1[Velocity],1):INDEX(Table1[Velocity],J8))``

Thanks!

Thank you!

# Re: Help with Formula

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!

``= AVERAGEIFS(Velocity, Sprints,">"&(COUNT(Sprints)-usedSprints))``

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.