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

	a	b	c	TOTAL	TARGET	VARIANCE
R1	1		1	2	3	-1
R2	1	1		2	2	0
R3	1		1	2	1	1
TOTAL	3	1	2	6	6	0
TARGET	2	2	2	6
VARIANCE	1	-1	0

<tbody>
</tbody>

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.