I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's
 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 thisa) with a user-defined functionb) via worksheet functions
 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)=LARGE(IF(\$A\$2:\$A\$99=\$C7,ROW(\$A\$2:\$A\$99)),D\$6),IF(\$A\$2:\$A\$99=\$C7,\$B\$2:\$B\$99))))
 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.
 Sulprobil   Get it done   Contact   Disclaimer   Impressum   Download