Average last 3 in range

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

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
 
L

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

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

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
 
G

Glenn

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


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

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

Luke M

Corrected formula, still an array:

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

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

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
 
G

Glenn

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

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

Glenn

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,


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

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

Glenn

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.


That works!
 

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

Top