Ian Bell
asked on
suitable program for data manipulation
Hi,
I am wondering what program could be used for the attached project.
I'm wanting to find what variable weightings produce the maximum profit.
Is this a form of regression and if so which one ?
Please manually input values into cell range M2:S2 and notice how it affects P/L cell (Yellow)
Many thanks for any advice you may have
Ian
Regression-Weightings.xlsx
I am wondering what program could be used for the attached project.
I'm wanting to find what variable weightings produce the maximum profit.
Is this a form of regression and if so which one ?
Please manually input values into cell range M2:S2 and notice how it affects P/L cell (Yellow)
Many thanks for any advice you may have
Ian
Regression-Weightings.xlsx
ASKER
Hi Fred,
Thanks for taking the time to respond.
Currently using positive integers but will also consider negative ones if that helps the
bottom line.
I was aiming to use up to an absolute max of var^4 the measure of any one variable to the power of 4.
I have written a series of Excel formulas whereby I change the weightings manually similar to that sheet I
quickly knocked up and attached.
In terms of "outcome" I would be looking at an ROI of around 25%+ that would be the Profit / total INV
I suppose you were thinking of a VBA solution ?
Thanks for taking the time to respond.
Currently using positive integers but will also consider negative ones if that helps the
bottom line.
I was aiming to use up to an absolute max of var^4 the measure of any one variable to the power of 4.
I have written a series of Excel formulas whereby I change the weightings manually similar to that sheet I
quickly knocked up and attached.
In terms of "outcome" I would be looking at an ROI of around 25%+ that would be the Profit / total INV
I suppose you were thinking of a VBA solution ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've tried running Solver on sample sheet provided previously and using Evolutionary with the below settings.
Set Objective $I$1
Set to Max
changing variable cells $L$2:$R$2
I tried with constraints each cell $L$2 through to $R$2 set at >=0
and also tried without any constraints
But no changes to range
What am I missing ?
Thanks
Ian
Set Objective $I$1
Set to Max
changing variable cells $L$2:$R$2
I tried with constraints each cell $L$2 through to $R$2 set at >=0
and also tried without any constraints
But no changes to range
What am I missing ?
Thanks
Ian
ASKER
I still require some guidance on this if you or anyone can help.
Thanks
Ian
Thanks
Ian
ASKER
Comments anyone ? It appears an easy add-in but I have a sticking point ... see above....Thanks
The variables have to be constrained at both their minimum and maximum values. You didn't mention that.
And, are the variables restricted to positive integers or....?