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

Solver-problem.doc
bitolexusAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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:
=SUM(F2:F19)
3) Use D2 and E2 as the "By changing" cells
4) Create a constraint cell with the formula:
=E2-((-1.4*10^-6*D2^3)+(0.0003*D2^2)-(0.11*D2)-9.3553)
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.

Brad
0
 
byundtCommented:
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.

Brad
0
 
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?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
byundtCommented:
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.
0
 
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>>
0
 
bitolexusAuthor Commented:
Good work Brat
0
 
byundtCommented:
bito,
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!
Brad
0
All Courses

From novice to tech pro — start learning today.