Hi,

One possibility:

In G5 enter the formula and click ENTER,

=SUM(A1:A5)

Autofill the formula across H5, I5, J5, K5, and L5, and autofill the
formulas down to the last row (e.g., G13, H13, I13, J13, K13, and L13).
These formulas calculate the running 5-row sum for each column (A thru F)
cascading down through rows.

Now, to find the maximum of these sums for each column and also the
corresponding row numbers:

In G1, enter the following formula, and autofill across H1, I1, J1, K1, and
L1.

=MAX(G5:G13) ---- change the "13" to actual last row number.

In G2, enter the following formula, and autofill across H2, I2, J2, K2, and
L2.

=MATCH(MAX(G$5:G$13),G$5:G$13,0)&" - "&MATCH(MAX(G$5:G$13),G$5:G$13,0)+4
--- again, change the "13"s to actual last row number.

PS: Note that these formulas work only if the data start with Row 1. If
they don't, the formulas have to be modified slightly.


Regards,
B. R. Ramachandran









"Jennifer Q" wrote:

A user posed this problem and I have no idea how to help him out. Any
suggestions or advice?

Thanks in advance for your help.

Jennifer Q

~~~~~~~
Suppose that I have a matrix that has say 13 rows and 6 columns. I wish to
write an EXCEL program that takes 5 rows at a time and computes the sum of
each column for the resulting matrix. The program is to record the maximum
of these column sums along with the rows for which the computation was made.
The program is to cycle through all 1287 possible combinations of five rows
repeating the column sum computations and recordings as indicated above.



An example matrix is shown below:



| A | B | C | D | E | F

1 | 1 2 1 1 4 8

2 | 5 8 7 4 1 5

3 | 9 6 2 9 9 6

4 | 6 5 5 7 7 7

5 | 11 13 6 3 3 2

6 | 8 7 11 2 5 10

7 | 3 12 4 10 8 4

8 | 4 1 8 12 12 11

9 | 12 3 10 8 10 1

10| 7 9 13 13 6 3

11| 10 10 12 11 2 13

12| 2 4 9 6 13 12

13| 13 11 3 5 11 9