Forum Discussion
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...
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
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.
- Thank you very much, HansVogelaar!
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...- Thanks!
