Can Excel do this?!

ALCE_BA

New member
Joined
Oct 13, 2014
Messages
2
Reaction score
0
Points
0
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
 
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?
 
Re:

Yes -- Two separate people working on the same problem. I was unaware that he also posted on this forum.
 
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.
 
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:
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?
 
Last edited:
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?
 
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.
 
Back
Top