Average last 3 in range

[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,

 

[Mike H]

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

 

[Luke M]

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.

 

[Luke M]

I think Mike H meant to use the COLUMN function, not ROW, but I like this
function more than the one I posted.

 

[Preschool Mike]

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

 

[Glenn]

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

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

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

 

[Luke M]

Corrected formula, still an array:

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

 

[T. Valko]

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.

 

[Preschool Mike]

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

 

[Glenn]

How do you want it fixed? What result do you want for 0, 1 or 2 numbers found
in the range?

 

[Preschool Mike]

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,

 

[Glenn]

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,

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...

 

[Domenic]

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.

 

[Glenn]

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.

That works!