Formula Help
 

For any Row:

Columns 1-24 contain whole numbers.

Column 25 contains a whole number which is less than 8, representing
the number of columns to consider when averaging values.

Column 26 contains a formula (or resulting value) indicating the
beginning column, which has the highest average value, for those
columns averaged, based on the value in column 25.

So if Column 25=5, then Column 26 would average columns 1-5, then 2-6,
then 3-7, 4-8, etc. to determine which of these averages is highest,
and indicate the beginning Column Number of that average in Column 26.

I hope I have explained this well enough for someone to help.

Formula Help
 

On Wed, 22 Jul 2009 17:03:59 -0700 (PDT), iamnu
wrote:

For any Row:

Columns 1-24 contain whole numbers.

Column 25 contains a whole number which is less than 8, representing
the number of columns to consider when averaging values.

Column 26 contains a formula (or resulting value) indicating the
beginning column, which has the highest average value, for those
columns averaged, based on the value in column 25.

So if Column 25=5, then Column 26 would average columns 1-5, then 2-6,
then 3-7, 4-8, etc. to determine which of these averages is highest,
and indicate the beginning Column Number of that average in Column 26.

I hope I have explained this well enough for someone to help.

With your numbers to be averages in cells A1:X1, the number of cells
to average in cell Y1, try the following formula in cell Z1:

=MATCH(MAX(MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET(A 1:A1,,,,24)))<
(ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(ROW (A2:A25)-Y1))))),
MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET(A1:A1,,,,24) ))<
(ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(ROW (A2:A25)-Y1)))),0)+2-Y1

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

Formula Help
 

On Thu, 23 Jul 2009 07:18:04 GMT, Lars-Åke Aspelin
wrote:

On Wed, 22 Jul 2009 17:03:59 -0700 (PDT), iamnu
wrote:

For any Row:

Columns 1-24 contain whole numbers.

Column 25 contains a whole number which is less than 8, representing
the number of columns to consider when averaging values.

Column 26 contains a formula (or resulting value) indicating the
beginning column, which has the highest average value, for those
columns averaged, based on the value in column 25.

So if Column 25=5, then Column 26 would average columns 1-5, then 2-6,
then 3-7, 4-8, etc. to determine which of these averages is highest,
and indicate the beginning Column Number of that average in Column 26.

I hope I have explained this well enough for someone to help.

With your numbers to be averages in cells A1:X1, the number of cells
to average in cell Y1, try the following formula in cell Z1:

=MATCH(MAX(MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET( A1:A1,,,,24)))<
(ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(RO W(A2:A25)-Y1))))),
MMULT(A1:X1,TRANSPOSE(((COLUMN(OFFSET(A1:A1,,,,24 )))<
(ROW(A3:A26)))*((COLUMN(OFFSET(A1:A1,,,,24)))(RO W(A2:A25)-Y1)))),0)+2-Y1

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke


The previously posted formula does not handle negative numbers
correctly. I hope this one will do that:

=MATCH(MAX(IF(COLUMN(A1:X1)=Y1,MMULT(A1:X1,TRANSP OSE(
((COLUMN(A1:X1))<(ROW(A2:A25)))*((COLUMN(A1:X1))( ROW(A1:A24)-Y1)))))),
IF(COLUMN(A1:X1)=Y1,MMULT(A1:X1,TRANSPOSE(((COLUM N(A1:X1))<
(ROW(A2:A25)))*((COLUMN(A1:X1))(ROW(A1:A24)-Y1))))),0)+1-Y1

Note: This is also an anrray formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke