Dynamic average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am looking for a way to average a series of number in a column. I would
like this formula to average the numbers up to a quantity of 20. So, if
there are 3 numbers in the column, I would like it to average those 3. If
there are 23, I would like it average the last 20. Thanks, Mike.
 
Say your numbers were in A1 to A100, try this *array* formula:

=AVERAGE(A100:INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100<>""),20)))
 
Back
Top