Solver Non linear constraint problem

A team of archaeologists are working on a site in South America. Over the past few months a number of interesting discoveries have been made. All the discoveries lay within a grid which is 1000 square metres in size. The coordinates of each dig site are given in attached file.
The dig sites have started to reveal an increasingly large number of artefacts. The site manager would like to build a headquarters within the 1000 m2 grid where all the new discoveries can be collected and examined. To reduce complaints from workers he would like to position the new building in such a way that the total distance from the headquarters to all sites is at a minimum. The distance between a particular dig site and the headquarters can be found using the equation below.
SQRT((Xhq-Xs)^2 + (Yhq-Ys)^2)
What is the coordinates of the best position of the new headquarters?
More details of the problem see attached file

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
As this is an academic exercise, the Expert community doesn't want to get you in trouble with the professor. It would be helpful if you told us what you had tried and where you got stuck.

The trick in using Solver to optimize the location of the headquarters is to add up the distances from each dig site to the location of the headquarters. You may pick the initial location of the headquarters arbitrarily using two cells to hold the x and y co-ordinates. The Goal will be for Solver to minimize the sum of those distances to the headquarters, by varying the co-ordinates of the headquarters.

bitolexusAuthor Commented:
I have found the HQ without the river constraint. the problem I have is that the constraint is an equation and I don't know how handle that with solver. how do i find the best location for the HQ on the northern side of the river?
byundtMechanical EngineerCommented:
Your problem statement gives y as a function of x. If you put that formula in a cell (as a function of the headquarters x position) and subtract from it the headquarters y position, then you are left with a cell whose value needs to be positive. That's the constraint for Solver.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

bitolexusAuthor Commented:
Here is what I have done probably I am not understanding what you are saying. I have attached my excel file look at sheet 2c.

<<file removed per Asker request--byundt, Microsoft Excel Zone Advisor  3-28-10>>
byundtMechanical EngineerCommented:
I changed your model on worksheet 2c as follows:
1) Delete all of your existing constraints
2) Create a cell for the Goal with the formula:
3) Use D2 and E2 as the "By changing" cells
4) Create a constraint cell with the formula:
This constraint on this cell is >=0

I didn't like having negative distances in column F, and so changed the sign to positive. That meant my Goal was to minimize the sum. You could equally well leave the negative distances in column F and try to maximize the sum as the Goal.

Solver will now find the best place on the northern side of the river.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bitolexusAuthor Commented:
Good work Brat
byundtMechanical EngineerCommented:
I see that this is your first question on Experts Exchange. I hope that the experience met your expectations.

Normally on work related problems like this, you would get a complete solution to your problem fairly quickly. On academic related questions, we try to avoid getting people in trouble with their profs by offering general discussions and helping you where you get stuck. Posting your workbook with your unsuccessful attempt to solve problem c was perfect.

Thanks for the kind words and grade!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.