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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I tried to share the data however it didn’t let me using the excel tool

please advise what the best possible solution is....

its trying to replicate the goalseek in formula but for every cell in range and not 1 cell but distribution it evenly as best as it can within the -20% 20% variance through the day
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Any suggestions will be appreciated
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hiya

I was unable to use the XL2BB so ive put an example of data before and after

Say Data Set was like this before

Req % Formula copied down =C4/$C$17
Ava % Formula copied down =D4/$D$17
Ind % (Variance From Required) formula copied down =(F4-E4)/E4


RequiredAvailableReq %Ava %Ind %
08:00​
7​
4​
2.22%2.00%-10.00%
08:30​
29​
25​
9.21%12.50%35.78%
09:00​
5​
3​
1.59%1.50%-5.50%
09:30​
14​
12​
4.44%6.00%35.00%
10:00​
24​
18​
7.62%9.00%18.13%
10:30​
12​
11​
3.81%5.50%44.38%
11:00​
28​
26​
8.89%13.00%46.25%
11:30​
19​
13​
6.03%6.50%7.76%
12:00​
24​
20​
7.62%10.00%31.25%
12:30​
17​
8​
5.40%4.00%-25.88%
13:00​
46​
20​
14.60%10.00%-31.52%
13:30​
45​
20​
14.29%10.00%-30.00%
14:00​
45​
20​
14.29%10.00%-30.00%
315​
200​

My Aim is to get the Ind% As close to within -20% and 20% (So distributing the Available throughout day so every interval is within the target but spread out as best as possible

If I do manually - The Available should look like this

-20%​
20%​
RequiredAvailableReq %Ava %Ind %Revised Available vs Original
08:00​
7​
5​
2.22%2.50%12.50%1
08:30​
29​
22​
9.21%11.00%19.48%-3
09:00​
5​
3​
1.59%1.50%-5.50%0
09:30​
14​
10​
4.44%5.00%12.50%-2
10:00​
24​
18​
7.62%9.00%18.13%0
10:30​
12​
9​
3.81%4.50%18.13%-2
11:00​
28​
20​
8.89%10.00%12.50%-6
11:30​
19​
14​
6.03%7.00%16.05%1
12:00​
24​
18​
7.62%9.00%18.13%-2
12:30​
17​
9​
5.40%4.50%-16.62%1
13:00​
46​
24​
14.60%12.00%-17.83%4
13:30​
45​
24​
14.29%12.00%-16.00%4
14:00​
45​
24​
14.29%12.00%-16.00%4
315​
200​

Hope this makes more sense
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I managed to use the xl2bb to copy it over - hopefully you can help - thank you

Book1
BCDEFGHIJKLMNOP
1-20%20%
2
3RequiredAvailableReq %Ava %Ind %RequiredAvailableReq %Ava %Ind %Revised Numbers
408:00742.22%2.00%-10.00%08:00752.22%2.50%12.50%1
508:3029259.21%12.50%35.78%08:3029229.21%11.00%19.48%-3
609:00531.59%1.50%-5.50%09:00531.59%1.50%-5.50%0
709:3014124.44%6.00%35.00%09:3014104.44%5.00%12.50%-2
810:0024187.62%9.00%18.13%10:0024187.62%9.00%18.13%0
910:3012113.81%5.50%44.38%10:301293.81%4.50%18.13%-2
1011:0028268.89%13.00%46.25%11:0028208.89%10.00%12.50%-6
1111:3019136.03%6.50%7.76%11:3019146.03%7.00%16.05%1
1212:0024207.62%10.00%31.25%12:0024187.62%9.00%18.13%-2
1312:301785.40%4.00%-25.88%12:301795.40%4.50%-16.62%1
1413:00462014.60%10.00%-31.52%13:00462414.60%12.00%-17.83%4
1513:30452014.29%10.00%-30.00%13:30452414.29%12.00%-16.00%4
1614:00452014.29%10.00%-30.00%14:00452414.29%12.00%-16.00%4
17315200315200
Sheet1
Cell Formulas
RangeFormula
E4:E16,M4:M16E4=C4/$C$17
F4:F16,N4:N16F4=D4/$D$17
G4:G16,O4:O16G4=(F4-E4)/E4
P4:P16P4=L4-D4
C17:D17,K17:L17C17=SUM(C4:C16)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,123
This kind of problem potentially requires changing 13 cells, each of which could be multiple values. Although I can vaguely picture a formula (actually several formulas), that's probably not the best way. A VBA approach could work, but you can also try the Solver. It comes with Excel, but isn't installed by default. If you don't have it installed, click File > Options > Add-ins > On the bottom click Go... > check Solver > OK.

Starting with your sheet from post 5, I added a few formulas like this:

Book1
ABCDEFGHIJKLMNOPQ
1-20.00%20.00%
2
3RequiredAvailableReq %Ava %Ind %RequiredAvailableReq %Ava %Ind %Revised Numbers
40.333333742.22%2.00%-10.00%0.333333752.22%2.50%12.50%11
50.35416729259.21%12.50%35.78%0.35416729229.21%11.00%19.48%-33
60.375531.59%1.50%-5.50%0.375531.59%1.50%-5.50%00
70.39583314124.44%6.00%35.00%0.39583314104.44%5.00%12.50%-22
80.41666724187.62%9.00%18.13%0.41666724187.62%9.00%18.13%00
90.437512113.81%5.50%44.38%0.43751293.81%4.50%18.13%-22
100.45833328268.89%13.00%46.25%0.45833328208.89%10.00%12.50%-66
110.47916719136.03%6.50%7.76%0.47916719146.03%7.00%16.05%11
120.524207.62%10.00%31.25%0.524187.62%9.00%18.13%-22
130.5208331785.40%4.00%-25.88%0.5208331795.40%4.50%-16.62%11
140.541667462014.60%10.00%-31.52%0.541667462414.60%12.00%-17.83%44
150.5625452014.29%10.00%-30.00%0.5625452414.29%12.00%-16.00%44
160.583333452014.29%10.00%-30.00%0.583333452414.29%12.00%-16.00%44
1731520031520030
Sheet4
Cell Formulas
RangeFormula
M4:M16,E4:E16E4=C4/$C$17
N4:N16,F4:F16F4=D4/$D$17
O4:O16,G4:G16G4=(F4-E4)/E4
P4:P16P4=L4-D4
Q17,K17:L17,C17:D17C17=SUM(C4:C16)
Q4:Q16Q4=ABS(P4)


Then go to the Data tab, click the Solver button on the far right, and set up the parameters like this:

1615574662290.png


The Q4 formula is just the absolute value of the difference you have in P4, and the sum in Q17 is a measure of how many changes you have to make. The basic logic of this set up is that Solver will change all the values in L4:L16, between 0 and K4:K16, making sure that O4:O16 stays between N1 and O1, and the total in L17 stays the same as D17, and tries to minimize the number of changes (Q17) needed to make that happen. If you run the Solver from your starting point, it will find a slightly "better" solution that what you found.

Good luck!
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you so much - i will give it a go
Only problem is that column L is what i typed in manually to get the best fit which is what im trying to avoid - so it mimics what ive put in Col L if thats makes sense without me having to fill that in manually
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
and also again i want to say thank you so so so much - i really appreciate it and cant wait to try it...Couple of Questions if thats ok

1. If there isn’t a period where it cant be within the -20% or 20% target, would the solver get it to as close at it can so the ain is to get most of the periods spread out evenly within target (it may already do that but thought id ask)

2. I have 7 sheets exactly the same that needs to perform the same solver (exactly same cells and layout etc) just the same for 7 days. So my question was can i use the solver assigned to a macro and loop through those 7 sheets and populate?

3. And lastly, i have several workbooks with the same set up however the range on different workbooks can vary ie instead of 4:16 it could be 4:18 can i dynamically set the range in the solver even if i could do it by VBA

so on calc sheet say i set start row and end row and then use those rows in the variable
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,123
Only problem is that column L is what i typed in manually to get the best fit which is what im trying to avoid - so it mimics what ive put in Col L if thats makes sense without me having to fill that in manually
You don't need to fill in column L. Start with it empty and the Solver will fill it.

1. If there isn’t a period where it cant be within the -20% or 20% target, would the solver get it to as close at it can so the ain is to get most of the periods spread out evenly within target (it may already do that but thought id ask)
As it's set up, if it can't solve the problem, it will just quit and not give any result. It may be possible to minimize the amount that exceeds your boundaries, and maybe even minimize the number of changes in case of a tie. But that would make the problem more complex, and the Solver may not be able to solve it.

2. I have 7 sheets exactly the same that needs to perform the same solver (exactly same cells and layout etc) just the same for 7 days. So my question was can i use the solver assigned to a macro and loop through those 7 sheets and populate?
Yes, the Solver can be called from a macro, and look at different sheets.

3. And lastly, i have several workbooks with the same set up however the range on different workbooks can vary ie instead of 4:16 it could be 4:18 can i dynamically set the range in the solver even if i could do it by VBA
Yes, the macro can look at different workbooks, and dynamically set the ranges. Experiment with the model as it stands now though to see if it works well enough for you. Different values, different number of rows, etc. No point in coding a macro if this won't work.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you so much

I will give it a go - you are right - before i code i would like to get it to best fit if it doesn’t meet the criteria rather than quit and not do anything as there may be cases where i just cant get it to fit within criteria

if there is a way you can figure something to get around minimizing the boundaries that will be awesome but ill give this a go for now

thank you and will get back to you :)
 

Forum statistics

Threads
1,136,795
Messages
5,677,783
Members
419,720
Latest member
kurman

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