Link to home
Start Free TrialLog in
Avatar of Ian Bell
Ian BellFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of hypercube
hypercube
Flag of United States of America image

I think that Excel can do what you want.  But, "what you want" isn't clear.  What sort of outcomes are wanted?  High?  Low?
And, are the variables restricted to positive integers or....?
Avatar of Ian Bell

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 ?
ASKER CERTIFIED SOLUTION
Avatar of hypercube
hypercube
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
I still require some guidance on this if you or anyone can help.
Thanks
Ian
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.