SOLVED

Help with Formula

Copper Contributor

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

 

Google Sheets formula:

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

 

Can you please assist based on sample spreadsheet attached?

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. 

 

Thank you in advance!

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

@ragomes1972 

In N8:

 

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

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

@ragomes1972 

As variant

=AVERAGE(INDEX(Table1[Velocity],1):INDEX(Table1[Velocity],J8))
Thanks!
Thank you!

@ragomes1972 

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.

1 best response

Accepted Solutions
best response confirmed by ragomes1972 (Copper Contributor)
Solution

@ragomes1972 

In N8:

 

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

View solution in original post