Help on Solver add-in
Hi,
I would like to know how to add variable range and upper and lower boundaries using Excel solver
Would you kindly help by applying to attached sheet.
Objective cell $i$1
Changing Variable range $L$2:$R$2
Variable range $L$4:$R$33
Many thanks
Ian
Regression-Weightings-Martin.xlsx
I would like to know how to add variable range and upper and lower boundaries using Excel solver
Would you kindly help by applying to attached sheet.
Objective cell $i$1
Changing Variable range $L$2:$R$2
Variable range $L$4:$R$33
Many thanks
Ian
Regression-Weightings-Martin.xlsx
ASKER
I don't follow your answer........ "This means"...... what does ?
ok sorry
I changed on your attached file the following:
And when I pressed on Solve I got this:
Basis the error as the values requested exceed the standard version of the excel solver you will need to upgrade to a higher version that can handle more variable but this version is paying however as per the link you can notice that you have a trial period so maybe you can get your results with the trial version.
Hope above clarifies.
gowflow
I changed on your attached file the following:
And when I pressed on Solve I got this:
Basis the error as the values requested exceed the standard version of the excel solver you will need to upgrade to a higher version that can handle more variable but this version is paying however as per the link you can notice that you have a trial period so maybe you can get your results with the trial version.
Hope above clarifies.
gowflow
ASKER
That's not the message I get. Here's mine using same format you used
! All variables must have both upper and lower bounds.
Also what happens at your end if you selected a smaller range say $L$4:$R$10 ??
Thanks
! All variables must have both upper and lower bounds.
Also what happens at your end if you selected a smaller range say $L$4:$R$10 ??
Thanks
I am not familiar with solver at all never used it and do not really know what it does. So here you are the expert if you can brief me on the way the solver works and what do u put ... what are the different input and their meaning then I will be able to understand better and maybe provide help that may benefit you at the end.
Sorry but here it is reverse ... your the Expert !!! :)
gowflow
Sorry but here it is reverse ... your the Expert !!! :)
gowflow
yes I get this error
Let me know what it means together with the previous explanation.
gowflow
Let me know what it means together with the previous explanation.
gowflow
ASKER
Your response shocked me!
I think you have got this back to front. It is I who is asking for expert help.
I have never used Solver hence the question I put to Experts Exchange.
If you have never used Excel Solver why are you offering to help with this question ?
I think you have got this back to front. It is I who is asking for expert help.
I have never used Solver hence the question I put to Experts Exchange.
If you have never used Excel Solver why are you offering to help with this question ?
ASKER
Are there any Experts that are familiar with the Solver Add-in that can guide me
on how to set up the Solver Interface and help with upper and lower bounds ?
Many thanks
Ian
on how to set up the Solver Interface and help with upper and lower bounds ?
Many thanks
Ian
I am sorry if being honest offend you and shocked you. Simply press on the button request attention and ask for more help and moderator will send additional message seeking help or wait till this question becomes neglected. I thought I could help you as much as I could by interacting but seems you took it the wrong way.
One last issue I am NOT interested in points but care to help solving issues. Trust me, most of my Interactions here in EE I didn't know about when I first started it is only in asking, trying and having the courage to go forward and learn that one move forward.
Best of luck in your endeavors and hope you will get the help you deserve.
Regards
gowflow
One last issue I am NOT interested in points but care to help solving issues. Trust me, most of my Interactions here in EE I didn't know about when I first started it is only in asking, trying and having the courage to go forward and learn that one move forward.
Best of luck in your endeavors and hope you will get the help you deserve.
Regards
gowflow
ASKER
I am still not sure why you assumed I was an expert.
Anyway thanks for your frank reply and willingness to participate but
it appears in this case it would be a matter of the bind leading the blind.
More power to you for taking the time to help others and learn as you go.
Kind regards
Ian
Anyway thanks for your frank reply and willingness to participate but
it appears in this case it would be a matter of the bind leading the blind.
More power to you for taking the time to help others and learn as you go.
Kind regards
Ian
Dear Ian,
By Expert I meant you are more knowledgeable in this part of the subject as for me It was the first time I came across it. Expert means nothing !!! Don't be fooled by titles I believe we are all students and all seeker of knowledge and help.
Good luck
gowflow
By Expert I meant you are more knowledgeable in this part of the subject as for me It was the first time I came across it. Expert means nothing !!! Don't be fooled by titles I believe we are all students and all seeker of knowledge and help.
Good luck
gowflow
ASKER
An expert by 40 minutes as that is all the time I had spent on it before I got stuck :)
Cheers
Cheers
Let me ask you a question then:
Why use solver ? who told you solver will get you to what you want ? this was my initial question ? What are you trying to achieve ? you did not bother explain what you want in plain English ... maybe I could help somehow !
gowflow
Why use solver ? who told you solver will get you to what you want ? this was my initial question ? What are you trying to achieve ? you did not bother explain what you want in plain English ... maybe I could help somehow !
gowflow
ASKER
What I am trying to do is find multipliers for each column in range $L$2:$R$2
The Solver suggestion was from a previous question but wasn't followed up.
The outcome is to have max profit in cell $I$1
To do this we have to find multipliers for each cell in range $L$2:$R$2
each multiplier should range from -4 to 4
The variable cells are in range $L$4:$R$33
If you manually input figures into the yellow cells in L2:R2 you will see the P/L change
Hope this explains things thanks again.
The Solver suggestion was from a previous question but wasn't followed up.
The outcome is to have max profit in cell $I$1
To do this we have to find multipliers for each cell in range $L$2:$R$2
each multiplier should range from -4 to 4
The variable cells are in range $L$4:$R$33
If you manually input figures into the yellow cells in L2:R2 you will see the P/L change
Hope this explains things thanks again.
ok I am trying to make sense out of the solver input you have made and wonder if they are correct.
1) You have put cells L4:L33 >-4 and L4:L33<4 but if you look at the MAX and MIN in all of L4 to R33 Columns here are the figures
Column L M N O P Q R
MAX 1.40 1.40 2.00 1.96 1.40 1.40 1.96
MIN 0.50 0.50 0.50 0.56 0.75 0.75 0.25
So basically the lowest is 0.25 and highest is 2 all positive values.
I don't understand the -4 to +4
Actually I drew the MIN and MAX for all columns and here are the findings:
For Col C Col D which are Rating
MAX 5.22 4.50
MIN 0.73 0.36
Here also you go from 0.36 to 4.50 all positive values.
So the question is: Why -4 to +4
If you can explain more the logic as I was able to make the solver work but still with no solution I fear that maybe the criteria's are not well configured.
Also what I noticed and please correct me if I am wrong: You are setting Cell I1 as maximum profit which is a cell derived from calculations in Col A to J that are in no way realated whatsoever with the columns you are applying the solver to which are Col L to R so could you please explain the logic and what you are trying to get this is maybe why the solver can't find anything as we are putting a target of 57 and giving values from 0 to 2 and he cannot reach this target this is why what I get is 0 zero !!!
Let me know
gowflow
1) You have put cells L4:L33 >-4 and L4:L33<4 but if you look at the MAX and MIN in all of L4 to R33 Columns here are the figures
Column L M N O P Q R
MAX 1.40 1.40 2.00 1.96 1.40 1.40 1.96
MIN 0.50 0.50 0.50 0.56 0.75 0.75 0.25
So basically the lowest is 0.25 and highest is 2 all positive values.
I don't understand the -4 to +4
Actually I drew the MIN and MAX for all columns and here are the findings:
For Col C Col D which are Rating
MAX 5.22 4.50
MIN 0.73 0.36
Here also you go from 0.36 to 4.50 all positive values.
So the question is: Why -4 to +4
If you can explain more the logic as I was able to make the solver work but still with no solution I fear that maybe the criteria's are not well configured.
Also what I noticed and please correct me if I am wrong: You are setting Cell I1 as maximum profit which is a cell derived from calculations in Col A to J that are in no way realated whatsoever with the columns you are applying the solver to which are Col L to R so could you please explain the logic and what you are trying to get this is maybe why the solver can't find anything as we are putting a target of 57 and giving values from 0 to 2 and he cannot reach this target this is why what I get is 0 zero !!!
Let me know
gowflow
ASKER
Sorry for late reply been very busy.
The idea is to find the max P/L $I$1 when changing values in L33:R33
As the numbers change in L2:R2 you will see the P?L figure changes.
Have you tried manually changing them.? If you do then you will notice the
variables in range (L4:R33
For example try inputting 3 into L2 you will notice all the variables change in L4:L33
Cell L4 was originally 1.10 and now is 1.33 The P/L changed from 57 to -79
The max I can get the P/L to is 1953 by placing -4 into cells L2:Q2 and 4 into cell R2
Now instead of doing all this manually I want to automate it and that is when I was recommended to use Solver
Hope this is clear and thanks again for your patience.
Ian
The idea is to find the max P/L $I$1 when changing values in L33:R33
As the numbers change in L2:R2 you will see the P?L figure changes.
Have you tried manually changing them.? If you do then you will notice the
variables in range (L4:R33
For example try inputting 3 into L2 you will notice all the variables change in L4:L33
Cell L4 was originally 1.10 and now is 1.33 The P/L changed from 57 to -79
The max I can get the P/L to is 1953 by placing -4 into cells L2:Q2 and 4 into cell R2
Now instead of doing all this manually I want to automate it and that is when I was recommended to use Solver
Hope this is clear and thanks again for your patience.
Ian
Well well well the n you have them around I think !!!
L2:R2 is the variable that should go -4 to +4
and not L4:R33 >4 and -4
Isn'it ?
gowflow
L2:R2 is the variable that should go -4 to +4
and not L4:R33 >4 and -4
Isn'it ?
gowflow
ASKER
Yes that is correct. Do you have the hang of it now ?
Yes I think I almost got it.
1) We want to maximize I1 this I agree
2) L2:R2 are in relation to I1 and could go from -4 to +4
3) then what is your profit range ???
You say:The max I can get the P/L to is 1953 by placing -4 into cells L2:Q2 and 4 into cell R2
Do you have a range for this ? as I see no value in L4:R33 in this whole issue they are formulas and they will act upon changing L2:R2 and consequently I1 will be affected.
I noticed that T4:Z33 are the basis for L4:R33 then I tried to use these as a range but I need a minimum for them and a maximum.
gowflow
1) We want to maximize I1 this I agree
2) L2:R2 are in relation to I1 and could go from -4 to +4
3) then what is your profit range ???
You say:The max I can get the P/L to is 1953 by placing -4 into cells L2:Q2 and 4 into cell R2
Do you have a range for this ? as I see no value in L4:R33 in this whole issue they are formulas and they will act upon changing L2:R2 and consequently I1 will be affected.
I noticed that T4:Z33 are the basis for L4:R33 then I tried to use these as a range but I need a minimum for them and a maximum.
gowflow
ASKER
There is no profit range as such, we are looking for the max profit.
The values that are generated in L2:R2 are multipliers. The object is to determine the
order of importance of each column.
As regards wanting a min and max for range $T$4:$Z$33 they will vary with each set of data
I suppose a simple formula will determine the min/max values.
It is the range $L$2:$R$2 which will be generated with min -4 and max 4
Any clearer?
Ian
The values that are generated in L2:R2 are multipliers. The object is to determine the
order of importance of each column.
As regards wanting a min and max for range $T$4:$Z$33 they will vary with each set of data
I suppose a simple formula will determine the min/max values.
It is the range $L$2:$R$2 which will be generated with min -4 and max 4
Any clearer?
Ian
EUREKA !!!! Found it
so far I got a P/L 2030 is that good enough for you ???
gowflow
so far I got a P/L 2030 is that good enough for you ???
gowflow
Now ... at 2125 ...
Press on Solver and you will see the settings have fun and enjoy !!!
If any question let me know.
PS By the way thank you for giving me the chance to learn how solver works and how powerful it is (that is for sure if one know how to set it up !!! as not obvious).
gowflow
Regression-Weightings-Martin-V01.xlsx
Press on Solver and you will see the settings have fun and enjoy !!!
If any question let me know.
PS By the way thank you for giving me the chance to learn how solver works and how powerful it is (that is for sure if one know how to set it up !!! as not obvious).
gowflow
Regression-Weightings-Martin-V01.xlsx
ASKER
I would like to know your secret as I tried entering exactly as you did but I get a message saying file too large. Have you
done anything else other than entering those fields?
But well done for working it out. Not sure why I can't make it work following same steps
done anything else other than entering those fields?
But well done for working it out. Not sure why I can't make it work following same steps
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is working now but I am falling asleep here so will recheck
in the morning.
Thanks
Ian
in the morning.
Thanks
Ian
ASKER
Any idea how to enter upper and lower bounds into the window ?
I'm trying this on another sheet and it is asking for them.
I'm trying this on another sheet and it is asking for them.
me too .... sleep ... will talk tomorrow
Good nite
Good nite
ASKER
Thanks Gowflow. You've been a great help.
Ian
Ian
Thank you Glad I could help,
Regarding the Upper and lower bound simply in Option go to evolutionary tab and untick lower upper bonds. Same if you choose an other method the Linear.
gowflow
Regarding the Upper and lower bound simply in Option go to evolutionary tab and untick lower upper bonds. Same if you choose an other method the Linear.
gowflow
ASKER
Thank you again.
http://www.solver.com/standard-excel-solver-dealing-problem-size-limits
gowflow