Goal Seek a Range Where the Total Variance of each Row and the Total Variance of each Column must be = 0

[vbavirgin]

Hi,

I will explain.See the table below[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]TOTAL[/TD]
[TD="align: center"]TARGET[/TD]
[TD="align: center"]VARIANCE[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]R1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]R2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]R3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]TARGET[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]VARIANCE[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The total target of the rows and columns will always equal each other and the total of the range will always equal the total target

I need to be able to goal seek each variance to zero by changing the values in the range A1:C3 (in this example). The real data more like 50 by 20

The values in the range can be decimals.

I am lost on this one - I assume an array could be used but that is way over my head at present.

I would appreciate some help on this one.

Thanks

 

[vbavirgin]

Hi all,

I manged to find a solution. It is slow but it works

The formula in the range A1:C3 takes its value e.g. A1 and adds a number in G1 and a number in A7. G1 and A7 are the ByChanging values to seek the TargetValue of 0

Sub ProRate()
    Dim i As Long
    Dim j As Long
    
    For j = 1 To 3
    For i = 1 To 3
    Cells(i, 6).GoalSeek Goal:=0, ChangingCell:=Cells(i, 7) 'make variance =0 by adding a value in column 7
    Cells(6, j).GoalSeek Goal:=0, ChangingCell:=Cells(7, j) 'make variance =0 by adding a value in row 7
    Next i
    Next j
    
 End Sub

Am open to any other suggestions.