SOLVED

Help with Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2299120%22%20slang%3D%22en-US%22%3EHelp%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299120%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20have%20to%20calculate%20the%20average%20sprint%20velocity%20from%20a%20table%20where%3A%3C%2FP%3E%3CP%3E-%20Column%20A%20%3D%20Sprint%20%23%20(1%20through%2028%20where%2028%20is%20at%20the%20top)%3C%2FP%3E%3CP%3E-%20Column%20B%20%3D%20sprint%20velocity%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20based%20on%20the%20above%20table%2C%20the%20user%20will%20select%20the%20number%20of%20sprints%20he%2Fshe%20wants%20to%20go%20back%20for%20the%20average%20velocity%20calculation.%20Say%20the%20user%20selects%206%20from%20the%20drop-down%2C%20then%20only%20the%20last%206%20sprints'%26nbsp%3B%20velocity%20should%20be%20used%20for%20the%20calculation%20of%20average%20velocity%20(sprints%2028%2C27%2C26%2C25%2C24%2C%20and%2023).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20already%20had%20this%20formula%20working%20on%20Google%20Sheets%2C%20I'm%20trying%20to%20convert%20it%20into%20Excel%20but%20haven't%20figured%20out%20a%20way%20to%20do%20so.%20Apparently%2C%20the%20formula%20ROW%20which%20returns%20a%20range%20can't%20be%20used%20with%20AVERAGEIF%20like%26nbsp%3B%20Google%20Sheets%20does...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGoogle%20Sheets%20formula%3A%3C%2FP%3E%3CP%3E%3DARRAYFORMULA(AVERAGEIF(ROW(INDIRECT(Veloc_SPs))-3%2C%20%3CSPAN%20class%3D%22%20string%20%22%3E%22%26lt%3B%3D%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%3EF8%3C%2FSPAN%3E%2CINDIRECT(Veloc_SPs))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20assist%20based%20on%20sample%20spreadsheet%20attached%3F%3C%2FP%3E%3CP%3EAlso%2C%20please%20notice%20that%20I%20have%20a%20cell%20with%20the%20velocity%20range%20from%20column%20B%20defined%20as%20a%20named%20Range%20so%20I%20can%20use%20that%20name%20in%20my%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2299120%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2299229%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024126%22%20target%3D%22_blank%22%3E%40ragomes1972%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20N8%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAVERAGE(OFFSET(%24B%241%2C1%2C0%2CJ8%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2299304%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299304%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E!%3CBR%20%2F%3EWhat%20if%20my%20table%20with%20Columns%20A%20and%20B%20is%20defined%20as%20a%20Table%20in%20Excel%2C%20how%20would%20I%20reference%20to%20%22%24B%241%22%20instead%3F%3CBR%20%2F%3EI%20tried%20transferring%20the%20formula%20but%20because%20I%20actually%20have%20it%20defined%20as%20a%20table%2C%20it's%20not%20working.%20Sorry%20about%20that...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2299430%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024126%22%20target%3D%22_blank%22%3E%40ragomes1972%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAVERAGE(INDEX(Table1%5BVelocity%5D%2C1)%3AINDEX(Table1%5BVelocity%5D%2CJ8))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2343301%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2343301%22%20slang%3D%22en-US%22%3EThanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2343302%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2343302%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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.