Random Matrixing Problem

[Jennifer Q]

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

 

[Guest]

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

 

[Jim Cone]

Jennifer,
Here is another approach.
(add a "Max" formula in a column to complete your request)
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'-----------------
'Combinations sub by John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Calls Comb2 sub.
'Creates the list in a single column.
'Select the top cell of the column then run code.

Sub Combinations()
Dim n As Variant
Dim m As Variant
ReStart:
n = InputBox("Number of items?", "Combinations")
If Len(n) = 0 Then Exit Sub
m = InputBox("Taken how many at a time?", "Combinations")
If Len(m) = 0 Then GoTo ReStart

Application.ScreenUpdating = False
Comb2 n, m, 1, vbNullString, ActiveCell
Application.ScreenUpdating = True
End Sub


'Comb2 sub by John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Generate combinations of integers k..n taken m at a time, recursively.

Sub Comb2(ByVal n As Integer, ByVal m As Integer, ByVal k As Integer, _
ByVal s As String, ByRef rng As Excel.Range)
If m > n - k + 1 Then Exit Sub
If m = 0 Then
rng.Value = RTrim$(s)
Set rng = rng(2, 1)
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", rng
Comb2 n, m, k + 1, s, rng
End Sub

'--------------------------
'Jim Cone - San Francisco, USA - 04/06/2006
'User must select the single column of values created by
'the Combinations sub before running this code.
'Assumes the matrix is on "Sheet2" in A1:F13 and that the
'Number of Items is 13 and are taken 5 at a time.

Sub GetColumnMaxtrixTotals()
Dim rngCell As Excel.Range
Dim rngMatrix As Excel.Range
Dim arr As Variant
Dim lngR As Long
Dim lngC As Long
Dim lngSum As Double

Set rngMatrix = Worksheets("Sheet2").Range("A1:F13")

Application.ScreenUpdating = False
For Each rngCell In Selection.Cells
arr = Split(rngCell.Value, " ")
For lngC = 1 To 6
For lngR = 0 To 4
lngSum = lngSum + rngMatrix(arr(lngR), lngC).Value
Next
rngCell.Offset(0, lngC).Value = lngSum
lngSum = 0
Next
Next
Application.ScreenUpdating = True
Set rngCell = Nothing
Set rngMatrix = Nothing
End Sub
'----------



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

 

[Harlan Grove]

Jennifer Q wrote...

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

So you're getting paid to answer this, but you're asking for free help
doing so? Are you going to tell your user where you found an answer?

~~~~~~~
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:

1 2 1 1 4 8
5 8 7 4 1 5
9 6 2 9 9 6
6 5 5 7 7 7
11 13 6 3 3 2
8 7 11 2 5 10
3 12 4 10 8 4
4 1 8 12 12 11
12 3 10 8 10 1
7 9 13 13 6 3
10 10 12 11 2 13
2 4 9 6 13 12
13 11 3 5 11 9

If you mean the user wants to keep track of the max column sum in each
column separately, it's trivial. Given the array above in A1:F13, the
max sum in col A would be

=SUM(LARGE(A1:A13,{1;2;3;4;5}))

and the rows involved would be given by

=MATCH(LARGE(A1:A13,n),A1:A13,0)

where n = 1..5.

On the other hand, if the user is keeping track of all the column sums
together, then they'd need to be combined into an ordinal metric which
you haven't specified.

 

[Jennifer Q]

WOW! Thanks for all the helpful suggestions. I'll share these with him and
see if they work for him.

Harlan,
I get paid to make sure his computer keeps working, not to come up with the
answers to squirrely problems like these. Of course I will tell him where
the answers came from. I would never take credit for something I don't quite
understand.

Thanks again, everybody, for your help!

Jennifer

 

[Jennifer Q]

Thanks for your idea, but this does not give us all 1287 combinations.