How to make a symmetric matrix where the sum of each row and column is k when the diagonal elements are certain (not zero) in excel?

Is there a simple algorithm that can be used in excel to build a symmetric matrix where the sum of each row and column is k when the diagonal elements are determined (not zero)? In other words, if the diagonal elements are determined, fill in the lower triangular matrix, and then transpose the lower triangular matrix, remove the diagonal elements, and merge into a new symmetric matrix, whose the sum of each row and column are all k?

For Example, I have three diagonal elements 2,4,5. Then there is a diagonal matrix in excel.

Then I make links to transpose the lower triangular matrix:

Then, I think I want to get a link that make the sum of each row and column is k=5:

Then excel will appear that

However, only the first two rows/first two columns of this matrix sum to 5. Does the algorithm I need exist?

I found a way to fill the lower triangular matrix of a given nXn symmetric matrix with diagonal elements such that the sum of the elements in each column or row of the matrix is k. First, fill the rest of the lower triangular matrix with any number so that the matrix has only n unknowns. These n unknowns are best presented as |-, so that excel can operate easily. Second, the lower half of the n unknowns of type |- (line 4 and the following) can be determined for n-3 unknowns using k-sum(Bi:Ni), such that only 3 unknowns remain. Third, if you look closely at these three unknowns, you can see that they just form the solution for the 3X3 matrix that I mentioned in the problem. Specifically, the principal diagonal elements of the 3X3 matrix. in which the principal diagonal elements of the 3X3 matrix are

X1=SUM(A1,D1:N1)，X2=SUM(B2,D2:N2)，X3=SUM(C3:N3)

Fourth, to solve the equations:

X1+A2+A3=k
A2+X2+B3=k
A3+B3+X3=k

Solved:

A2=k/2+(-X1-X2+X3)/2
A3=k/2+(-X1+X2-X3)/2
B3=k/2+(X1-X2-X3)/2

The solution of the equations is used to fill in the last three unknowns to obtain the symmetric matrix required by the problem.

Example:

First, I have three diagonal elements 2,4,5,6,7. Then there is a diagonal matrix in excel.

Second, make links to transpose the lower triangular matrix:

Third, filling some specific sections of cells, such as 1:

Fourth, n-3 elements in line 4 and the following can be determined using k-sum(Bi:Ni):

Fifth, using the solution of the above equation, filling the rest 3 elements of this matrix.

If you substitute X1, X2 and X3, you get

Let's set k=10 in this example, we have: