I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Function Example

If you have a list of names in column A and corresponding values in column B and you want to show the average of the last 3, 6, or 9 values for some (or all) names - if there are at least 3, 6, or 9 such names.
You can solve this
a) with a user-defined function
b) via worksheet functions

20110130_PB_01_sbAverageLast_Screen
20110130_PB_01_sbAverageLast_Code

Please notice that the data table in columns A and B of this example is going beyond row 10.

If you are interested in a 35 KB Excel 2010 © sample file go to my download page, please.

Biff (T. Valko) came up with the following worksheet function solution. Please notice that you have to enter this formula as an array formula. You need to enter it with CTRL + SHIFT + ENTER, not only with ENTER:

In cell D7:
=IF(COUNTIF($A$2:$A$99,$C7)<D$6,"",AVERAGE(IF(ROW($A$2:$A$99)>=LARGE(IF($A$2:$A$99=$C7,
ROW($A$2:$A$99)),D$6),IF($A$2:$A$99=$C7,$B$2:$B$99))))

20091101_PB_02_Function_Example

Despite the fact that Biff's formula is remarkably faster (have a look into the file link given above if you are interested) I suggest to prefer the macro function approach. The function code is encapsulated into one location only, and the name reference and the number of values for the average only need to be given once - which is less error prone.

A different but similar function is sbSumLast which sums up the last specified non-empty cells.

Back to VBA 03 Sub, Function, Params

Sulprobil   Get it done   Contact   Disclaimer   Download