Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Calculate Average
I would like to find the AVERAGE of the last "X" alues in a column. The
columnn contains an unknown number of empty cells. In the example below: COL A contains the values to be averaged. Cell B1 contains the variable "X" (3). Cell C1 should contain the formula for, and the value of (5), the average of the last "X" (3) values in COL A. Anyone have any suggestions? A B C 1 9 3 5<--- What is the formula??? 2 3 4 8 5 7 6 3 7 8 5 9 Thanks, K1NGR |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Calculate Average
=AVERAGE(A100:INDEX(A1:A100,SUMPRODUCT(LARGE(ROW(1 :100)*(A1:A100<""),B1))))
-- Gary''s Student - gsnu200770 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Calculate Average
Not sure if this is the best way, but it seems to work...
=AVERAGE(INDIRECT("A"&SUMPRODUCT(LARGE(ROW(A1:A100 )*(A1:A100<""),B1))&":A100")) Change the upper range (the 100s) to your maximum anticipated row of data. Rick "k1ngr" wrote in message ... I would like to find the AVERAGE of the last "X" alues in a column. The columnn contains an unknown number of empty cells. In the example below: COL A contains the values to be averaged. Cell B1 contains the variable "X" (3). Cell C1 should contain the formula for, and the value of (5), the average of the last "X" (3) values in COL A. Anyone have any suggestions? A B C 1 9 3 5<--- What is the formula??? 2 3 4 8 5 7 6 3 7 8 5 9 Thanks, K1NGR |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Calculate Average
It works great!
Rick's formula does it also (=AVERAGE(INDIRECT("A"&SUMPRODUCT(LARGE(ROW(A1:A10 0)*(A1:A100<""),B1))&":A100")) Thanks for the help Dick King "Gary''s Student" wrote: =AVERAGE(A100:INDEX(A1:A100,SUMPRODUCT(LARGE(ROW(1 :100)*(A1:A100<""),B1)))) -- Gary''s Student - gsnu200770 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Calculate Average
It works great!
Gary's Student's formula does it also =AVERAGE(A100:INDEX(A1:A100,SUMPRODUCT(LARGE(ROW(1 :100)*(A1:A100<""),B1)))) Thanks for the help Dick King "Rick Rothstein (MVP - VB)" wrote: Not sure if this is the best way, but it seems to work... =AVERAGE(INDIRECT("A"&SUMPRODUCT(LARGE(ROW(A1:A100 )*(A1:A100<""),B1))&":A100")) Change the upper range (the 100s) to your maximum anticipated row of data. Rick "k1ngr" wrote in message ... I would like to find the AVERAGE of the last "X" alues in a column. The columnn contains an unknown number of empty cells. In the example below: COL A contains the values to be averaged. Cell B1 contains the variable "X" (3). Cell C1 should contain the formula for, and the value of (5), the average of the last "X" (3) values in COL A. Anyone have any suggestions? A B C 1 9 3 5<--- What is the formula??? 2 3 4 8 5 7 6 3 7 8 5 9 Thanks, K1NGR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate an average if | Excel Discussion (Misc queries) | |||
Calculate average speed | Excel Discussion (Misc queries) | |||
CALCULATE WITH AVERAGE ???? | Excel Discussion (Misc queries) | |||
If/Then calculate the average | Excel Worksheet Functions | |||
CALCULATE AVERAGE BETWEEN WORKSHEETS | Excel Discussion (Misc queries) |