Average last 3 in range

  • Thread starter Thread starter Preschool Mike
  • Start date Start date
P

Preschool Mike

I'm having trouble coming up with a formula that only averages the last three
grades in a range. The range is X:BB and the grades are as percentages
(e.g., 100%). Note: Some cells in the range may not have data in them, so
the formula needs to overlook them and only average the last three grades
entered.

All help is appreciated.

Thanks,
 
Hi,

Try this

=AVERAGE(X1:BB1:INDEX(X1:BB1,LARGE(ROW(X1:BB1)*(X1:BB1<>""),3)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike
 
I believe this array* formula will work:

=AVERAGE(OFFSET(BB2,,,1,-(COLUMN(BB2)-LARGE(IF(ISNUMBER(X2:BB2),COLUMN(X2:BB2),"x"),3)+1)))

*Array formulas need to be confirmed using Ctrl+Shift+Enter, not just Enter.
 
I think Mike H meant to use the COLUMN function, not ROW, but I like this
function more than the one I posted.
 
Thanks for your help. I tried what you suggested but it's still returning
the average of the entire row and not just the last three grades entered.
I'm sorry but I did leave out that this is the range of a row. X10:BB10
 
Preschool said:
Thanks for your help. I tried what you suggested but it's still returning
the average of the entire row and not just the last three grades entered.
I'm sorry but I did leave out that this is the range of a row. X10:BB10
Click to expand...


Try this array formula (commit with CTRL+SHIFT+ENTER):

=AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(X10:BB10<>"",
COLUMN(X10:BB10),""),3))&":BB"&ROW()))
 
Corrected formula, still an array:

=AVERAGE(BB10:INDEX(10:10,LARGE(COLUMN(X10:BB10)*(X10:BB10<>""),3)))
 
Try this array formula** :

=AVERAGE(BB1:INDEX(X1:BB1,LARGE(IF(X1:BB1<>"",COLUMN(X1:BB1)-COLUMN(X1)+1),3)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

It assumes there will *always* be at least 3 numbers to average.
 
Thanks Glen, this one works great when 3 or more numbers are entered.
However I get a Number Error if less than 3 numbers are entered. Is there
anyway to fix this error

Mike Mast
Special Education Preschool Teacher
 
Is this doable? 0 numbers no error message, 1 or 2 numbers the average of the
2, more than 3 numbers the average of the last three in the range. If this
isn't doable then just something to clear up the number error when less than
3 numbers are entered.

Thanks so much,
 
Preschool said:
Is this doable? 0 numbers no error message, 1 or 2 numbers the average of the
2, more than 3 numbers the average of the last three in the range. If this
isn't doable then just something to clear up the number error when less than
3 numbers are entered.

Thanks so much,
Click to expand...


I can get it to handle 1 or 2 numbers by using:

=AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(X10:BB10<>"",
COLUMN(X10:BB10),""),MIN(COUNT(X10:BB10),3)))&":BB"&ROW()))


Still having trouble with the 0 numbers...
 
Try...

=IF(COUNT(X10:BB10),AVERAGE(INDEX(X10:BB10,LARGE(IF(X10:BB10<>"",COLUMN(X
10:BB10)-COLUMN(X10)+1),MIN(3,COUNT(X10:BB10)))):BB10),"")

....confirmed with CONTROL+SHIFT+ENTER.
 
Domenic said:
Try...

=IF(COUNT(X10:BB10),AVERAGE(INDEX(X10:BB10,LARGE(IF(X10:BB10<>"",COLUMN(X
10:BB10)-COLUMN(X10)+1),MIN(3,COUNT(X10:BB10)))):BB10),"")

...confirmed with CONTROL+SHIFT+ENTER.
Click to expand...


That works!
 
Back
Top