Find optimal values to satisfy constraints

Hello everyone!

I am dealing with one problem in Excel. I am attaching a workbook. What I basically have is a column with measured physical data, column A. If you plot that data, it is not very smooth and has lot of spikes. So the goal is to make a new column with smoother data and that would be in column B. This would however follow the same pattern, so increasing where A increases and decreasing where A decreases, but it would be smoother. Maybe like a 6 point average. However, this cannot be just smoother however a person likes, because other values are calculated from column B. And the constraint is that column G (Slue) must be 0 at the last two values (row 44 and 45).

What people currently do is that they try to manually make column B so that they get two 0s at the end of column G. They basically try different values until they get it right. However, this sometimes takes hours as even a little change causes the G column values to deviate by a lot. I was wondering if there is a way for Excel to calculate the best possible values for B column such that the last 2 values in G column are 0?

B column has to be integers only. The current workbook already contains a B column with values that satisfy the constraint and this would be a good solution. I obtained it from MATLAB by looping through multiple permutations and was lucky enough to land on a good solution since I can only do permutations of 10 different values at any time. Let me know if you think there is a way to do this in Excel!

Thank you

Auxcron

Originally Posted by MrShorty

I am optimistic that it can be done in Excel. At this point, it seems that it is more about algorithm development (which is outside of Excel) than programming. If you can help us understand the algorithm you want to use, we are usually pretty good at helping you program that algorithm into the spreadsheet. We are not always as effective at coming up with the algorithm from scratch.

It appears that you are currently using some kind of brute force try random permutations until you find one that you like. With 40ish different values to change over an undetermined range (only that it be "smoother" than the current set of values), there are a lot of possible permutations to try. Are you content with this kind of algorithm? What kind of algorithm do you use to generate new values? How are you measuring "smoothness"?

Something involving Excel's Solver might work, but that would necessitate coming up with suitable objective and constraint formulas (like how to quantify smoothness).

Thanks for your message, I appreciate it. Indeed this current algorithm is not feasible. I have got another example where it simply did not find a solution. Right now my algorithm first computes a 6 point average to give it a good starting point from which further adjustments can be made (This is already quite smooth). This solution is pretty close to resulting in the last two S values being 0. Now the next thing I do is that I choose start from the first point and take 10 digits to the right of this array and compute all the possible permutations of these 10 numbers, keeping numbers 11-42 the same. The range through which I do permutations is array number +(-1, 0, 1) so it either adds one to already existing number in array, does nothing or takes away one. If it does not find a solution within these first 10 numbers, it moves to do permutations on numbers 2-11, keeping number 1 and numbers 12-42 constant. This goes all the way to the end and stops once a solution is found. The reason why I do the permutations over 3 values -1, 0 and 1 is that since 6-point average is quite a good approximation, it only needs a little bit of adjustment somewhere, one value down or up. However, this is not going to work all the time as sometimes the 10 permutations are simply not enough and the solution doesn't lie within this method.

I was thinking of using least squares method to maybe compute a smooth graph but I wasn't able to do it in MATLAB using integers. I think a constraint for smoothness could be defined as how far a number is away from the 6-point moving average at a certain position. So for example I could compute 6-point average at the beginning and then say that the result can not lie more than +/-1 away from this average at any given point.

I also think that the solver is a way to go but not sure how to implement it in Excel for this problem and to be honest I have never used it before.

Hi Mrshorty,

thank you for your answer! I now have my objective function and all the constraints that I would need. Could you give me a hand on how I can write it into the Excel so I can use Excel solver?
My objective function is: obj = 2 * sum((DV(2:N) - DV(1:N-1)).^2) + 1/2 * sum((EV - DV).^2);
and my constraints are:
constraints = [constraints, d == DV-EV];
constraints = [constraints, d(1) == 0];
constraints = [constraints, ED(1) == 0];
constraints = [constraints, M(1) == 0];
constraints = [constraints, S(1) == 0];
% the constraint below is related to the -3 to 3 limit on delta DV
constraints = [constraints, -2 <= DV(2:N) - DV(1:N-1) <= 2];
constraints = [constraints, ED(2:N) == ED(1:N-1) + d(1:N-1)];
constraints = [constraints, M(2:N) == ED(2:N) + M(1:N-1)];
constraints = [constraints, S(2:N) == -2*(ED(1:N-1) + DV(1:N-1) - EV(1:N-1) + M(1:N-1))];
constraints = [constraints, S(end-1) == 0];
constraints = [constraints, S(end) == 0];
constraints = [constraints, S(14:28) >= 30];
constraints = [constraints, S <= 80];
constraints = [constraints, S >= -80];

It works well in MATLAB but need to put it into Excel somehow, I am not sure how to set it up. Any help will be much appreciated.

Hi MrShorty,

thank you for your help, I have been trying to work in Excel with this and i think I am getting somewhere thanks to you! Hopefully should be able to make it work.

Auxcron