# Thread: Can Excel do this?!

1. ## Can Excel do this?!

 A B C D 1 500 2 250 3 350 4 100 800 200

I am sure the answer has already been posted, but I am afraid I do not even know how to search for it - cause i am calling it the wrong thing.
I am an excel novice (I would put me at a 4.5 out of 10)
I want excel to auto fill in the blanks (A-C, 1-3) for me. Column D and Row 4 are totals for each row/column.

Obviously - I know Row 4 needs to equal Column D.

But is there a solver that can find the numbers that satisfy both sums?

can someone suggest a link or tutorial? Or even the right way to identify this "opportunity"?

Can Excel even do this calculation? Should I be using another program? The end result has to be Excel compatible in order for my output to generate properly.

Thanks  Reply With Quote

2. OK, so.... this post and this other one seem very similar.
Is this some homework question or a user with more than 1 user name?  Reply With Quote

3. ## Re:

Yes -- Two separate people working on the same problem. I was unaware that he also posted on this forum.  Reply With Quote

4. Isn't it odd that 'two separate people' post the same question, and even use the same words in the same order. Such a coincidence.  Reply With Quote

5. Remarkable coincidence I must say. Maybe it's a case of the left and right hand not talking to each other

Yes just use the solver, calculate sum of squares for rows and columns and solve for minimum. Constraints integer >= 0 I presume.
HTML Code:
```A    B    C    D
1    75    312    113    500    =(SUM(B2:D2)-E2)^2
2    0    225    25    250    =(SUM(B3:D3)-E3)^2
3    25    263    62    350    =(SUM(B4:D4)-E4)^2
4    100    800    200
=(SUM(B2:B4)-B5)^2    =(SUM(C2:C4)-C5)^2    =(SUM(D2:D4)-D5)^2

=SUM(F2:F4)+SUM(B6:D6)```
Just wondering if there is an easy way to do the array sum rather than 6 equations plus one total sum?  Reply With Quote

6. Aw man.....just so you know no one is pulling a "fast one" - I am the guy who created the first question. I lost hope - and my colleague with much more tenacity picked up the flag.
I (or we - yes we really are 2 people in the same company....) am going to have to play around with Solver.
The actual array we want to use this on is huge. Is there a limit to the solver?  Reply With Quote

7. you got off lightly mate, count your blessings you got an answer.
As far as I know it's constrained by your PC's memory.  Reply With Quote

data entry, excel, excel automatically sort, excel cells, excel charts 