Results 1 to 7 of 7

Thread: Can Excel do this?!

  1. #1

    Can Excel do this?!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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?

  3. #3

    Re:

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

  4. #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.

  5. #5
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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?
    Last edited by WizzardOfOz; 2014-10-14 at 04:43 AM. Reason: That looks pretty, Not!

  6. #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?

  7. #7
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •