Best Fit formula or VBA scenario

[mahmed1]

Hi All,

I hope you are well

I have a dataset that looks at what I require and how much I have available.

The % Of Total Requirement is the interval value over the total requirement for the day (Formula copied down =(F22/F$55)*100
The % Of % Of Total Available is the interval value over the total available for the day =(I22/I$55)*100

I then have a variance to see how far I am away from requirement =(AB22-AA22)/AA22

What I want to be able to do is set a target of either -20% or 20%. I want the variance to see how far I am away from requirement to be within that target spread across the day by changing the Availability

E.g I want to be able to spread out the availability across the day to get the % variance away from the target to be within my target (The aim is to evenly spread out the intervals within the target)
So a bit like the goal seek to change every value but spread out equally if that makes sense

It might be complexed formula or VBA approach

Hopefully someone can help me give a best fit scenario - Thank You So Much

 

[Eric W]

I see you've made progress, but I'm going to have to bow out at this point. I've experimented with your code, and the Solver, and the problem in general. Some conclusions I came to:

1) Somehow the Integer constraint is not being applied to the criteria range, this is why you're getting fractional results.

2) Some of your assignment statements may not be correct. For example:
ChangeCell = ws.Range("Q17").Address

probably should be:
ChangeCell = ws.Range("Q" & lRow + 1).Address

3) I also tried to suppress the message, and couldn't. The DisplayAlerts option only seems to apply to native Excel messages.

4) Overall, I pretty much came to the conclusion that the Solver is not a viable solution for your problem. It is highly dependent on the starting conditions and values. Some times it works, some times it doesn't. I tried some other formulas for the Q17 cell, but they didn't work any better. The factor that really doesn't change is that the problem involves trying integer values in the available column. The Solver was really designed to work on continuous functions. Requiring integers in the available column, and using ABS in the Q column causes discontinuities that make the algorithm hit and miss. It's really just trying each possibility and hoping to hit a solution. Ideally, a true model would have the situation that even if a tested possibility doesn't work, it still gives us some information about the direction we should go to find a solution. That doesn't work here. It would be easy enough to write a macro that goes through all the possibilities, and keeps the best, where the Solver only tries some of the possibilities. The problem there is that it would take a long time to run. Assuming the macro can try a million possibilities a second, this sample problem would still take over 2500 years to finish. Now there are much better optimization algorithms that don't require checking every possibility. But they are more complicated, and I'm afraid I just don't have the time to research and design one for you.

So I wish you well, and hope you find a solution, but I think this is more than I can handle in the time I have, and I'd daresay out of scope of any free site.

 

[mahmed1]

Thank You so much for taking time out to get back to me

i guess the best way for now is to use the linear approach and see if it works - if it doesn’t then i guess a manual approach will be quicker

i guess uve tried to see why it isnt using just integer as available but im glad i was in the right direction

i really do appreciate your help and time