Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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())) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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())) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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())) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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())) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE a range in a column if another column's range equals a val | Excel Discussion (Misc queries) | |||
Average if....between range | Excel Worksheet Functions | |||
Average of a , < range | Excel Worksheet Functions | |||
Can I sum or average a range with more than 1 condition? | Excel Discussion (Misc queries) | |||
Average Range | Excel Worksheet Functions |