How to Calculate Average

  • Thread starter Thread starter k1ngr
  • Start date Start date
K

k1ngr

I would like to find the AVERAGE of the last "X" alues in a column. The
columnn contains an unknown number of empty cells.

In the example below:
COL A contains the values to be averaged.
Cell B1 contains the variable "X" (3).
Cell C1 should contain the formula for, and the value of (5), the average
of the last "X" (3) values in COL A.

Anyone have any suggestions?

A B C
1 9 3 5<--- What is the formula???
2
3
4 8
5 7
6 3
7
8 5
9

Thanks,
K1NGR
 
Not sure if this is the best way, but it seems to work...

=AVERAGE(INDIRECT("A"&SUMPRODUCT(LARGE(ROW(A1:A100)*(A1:A100<>""),B1))&":A100"))

Change the upper range (the 100s) to your maximum anticipated row of data.

Rick
 
It works great!

Rick's formula does it also
(=AVERAGE(INDIRECT("A"&SUMPRODUCT(LARGE(ROW(A1:A100)*(A1:A100<>""),B1))&":A100"))

Thanks for the help
Dick King
 
It works great!

Gary's Student's formula does it also
=AVERAGE(A100:INDEX(A1:A100,SUMPRODUCT(LARGE(ROW(1:100)*(A1:A100<>""),B1))))


Thanks for the help
Dick King
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average problem 1
Average Calculations 11
Averaging unique values 2
Numbers from first column 5
Average Cells IF They Fit Criteria 3
Calculating Average 2
Calculating Average based on 3 filled cells from left in a row 6
How to calculate Average 5

Back
Top