Logical problem

Hi guys, I am brainstorming over a logic problem, if anyone could help
it would be greatly appreciated.

I have a spreadsheet with 9 columns and 9 rows, I want the sum of every
column and every row to be number 45, and if a number in a cell changes
then all numbers auto adjust to again produce 45.

Any clues?

Thanks !


--
kernelwiz

What drives the number change? Presumably if you have a 9x9 grid and
the number in row 4 column 6 changes, then you want that number to
remain static while other combinations are considered- correct? How
many numbers will need to be "locked" while checking combinations? Are
zeroes allowed?

The brute force approach is to write some FOR LOOP code that checks all
the possible combinations, and considers the static cells.

I'd use solver to do it. Let's say A1:I9 is your 9x9 grid, A10:I10 and J1:J9 is your SUM() of the corresponding row and column, and J10 is the grand total.

Set up solver:
1. Target cell = J10 -> value of 810 (45x18)
2. By changing cells A1:I9
3. Constraint A10:I10 = 45
4. Constraint J1:J9 = 45
5. Constraint A1:I9 = int (if you only want integer)
6. Run solver

Now let's say you want A1 = 6, simply remove A1 from "By changing cells" and run solver.


Hope it helps.

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("A1:I9")
If Not Intersect(Target.Cells, .Cells) Is Nothing Then
Application.EnableEvents = False
.Value = .Value
If Target.Row <> 9 Then
.Rows(9).Formula = "=45-SUM(A1:A8)"
.Columns(9).Formula = "=45-SUM(A1:H1)"
.Value = .Value
Else
.Rows(1).Formula = "=45-SUM(A2:A9)"
.Columns(1).Formula = "=45-sum(A2:I2)"
End If
.Value = .Value
Application.EnableEvents = True
End If
End With
End Sub


In article <[email protected]>,
kernelwiz <[email protected]> wrote:

> Hi guys, I am brainstorming over a logic problem, if anyone could help
> it would be greatly appreciated.
>
> I have a spreadsheet with 9 columns and 9 rows, I want the sum of every
> column and every row to be number 45, and if a number in a cell changes
> then all numbers auto adjust to again produce 45.
>
> Any clues?
>
> Thanks !

That's an incredibly theoretical question.. many more questions come from it
- Do you want the cell that changed to remain the same??
- do you want the other cells in the row that are changing to have a linear
change (ie they ALL change by the same value) or should they changed based on
how big they already are?? (weighted average)
etc, etc

Either way, you have to come up with your method.

To implement it, I believe you will have to trap the 'old' value of the cell
and the new value. That way you can take the difference and spread it out
(as you define it) over the othe cells in the row/column).

Set a module level variable in the selection_change event every time you
change the selection. This will be your old value (** Might store the old
cell address as well)

Then kick off the routine that updates columns and rows in the change event..

If you can't




"kernelwiz" wrote:

>
> Hi guys, I am brainstorming over a logic problem, if anyone could help
> it would be greatly appreciated.
>
> I have a spreadsheet with 9 columns and 9 rows, I want the sum of every
> column and every row to be number 45, and if a number in a cell changes
> then all numbers auto adjust to again produce 45.
>
> Any clues?
>
> Thanks !
>
>
> --
> kernelwiz
>