Best Fit formula or VBA scenario

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,123
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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,805
Messages
5,677,829
Members
419,723
Latest member
jamieherethree

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top