Average last 3 in range
 

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 Mast
Special Education Preschool Teacher

Average last 3 in range
 

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

"Preschool Mike" wrote:

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 Mast
Special Education Preschool Teacher

Average last 3 in range
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Preschool Mike" wrote:

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 Mast
Special Education Preschool Teacher

Average last 3 in range
 

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

Luke M
*Remember to click "yes" if this post helped you!*


"Mike H" wrote:

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

"Preschool Mike" wrote:

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 Mast
Special Education Preschool Teacher

Average last 3 in range
 

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
--
Mike Mast
Special Education Preschool Teacher


"Mike H" wrote:

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

"Preschool Mike" wrote:

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 Mast
Special Education Preschool Teacher

Average last 3 in range
 

Preschool Mike wrote:
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()))

Average last 3 in range
 

Corrected formula, still an array:

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

Luke M
*Remember to click "yes" if this post helped you!*


"Preschool Mike" wrote:

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
--
Mike Mast
Special Education Preschool Teacher


"Mike H" wrote:

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

"Preschool Mike" wrote:

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 Mast
Special Education Preschool Teacher

Average last 3 in range
 

Try this array formula** :

=AVERAGE(BB1:INDEX(X1:BB1,LARGE(IF(X1:BB1<"",COLU MN(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.

--
Biff
Microsoft Excel MVP


"Preschool Mike" wrote in message
...
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 Mast
Special Education Preschool Teacher

Average last 3 in range
 

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" wrote:

Preschool Mike wrote:
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()))

Average last 3 in range
 

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

Preschool Mike wrote:
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" wrote:

Preschool Mike wrote:
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()))

Average last 3 in range
 

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,
--
Mike Mast
Special Education Preschool Teacher


"Glenn" wrote:

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

Preschool Mike wrote:
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" wrote:

Preschool Mike wrote:
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()))

Average last 3 in range
 

Preschool Mike wrote:
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...

Average last 3 in range
 

Try...

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

....confirmed with CONTROL+SHIFT+ENTER.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Glenn wrote:

Preschool Mike wrote:
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...

Average last 3 in range
 

Domenic wrote:
Try...

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

...confirmed with CONTROL+SHIFT+ENTER.



That works!