Formula Help

[iamnu]

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.

 

[Lars-Åke Aspelin]

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)))>(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

 

[Lars-Åke Aspelin]

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)))>(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

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,TRANSPOSE(
((COLUMN(A1:X1))<(ROW(A2:A25)))*((COLUMN(A1:X1))>(ROW(A1:A24)-Y1)))))),
IF(COLUMN(A1:X1)>=Y1,MMULT(A1:X1,TRANSPOSE(((COLUMN(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

 

[smartin]

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,TRANSPOSE(
((COLUMN(A1:X1))<(ROW(A2:A25)))*((COLUMN(A1:X1))>(ROW(A1:A24)-Y1)))))),
IF(COLUMN(A1:X1)>=Y1,MMULT(A1:X1,TRANSPOSE(((COLUMN(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

Lars-Åke -- this is very good!