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

 

[mahmed1]

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]

Any suggestions will be appreciated

 

[mahmed1]

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

	Required	Available	Req %	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%​
	Required	Available	Req %	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]

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

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1													-20%	20%
2
3		Required	Available	Req %	Ava %	Ind %				Required	Available	Req %	Ava %	Ind %	Revised Numbers
4	08:00	7	4	2.22%	2.00%	-10.00%			08:00	7	5	2.22%	2.50%	12.50%	1
5	08:30	29	25	9.21%	12.50%	35.78%			08:30	29	22	9.21%	11.00%	19.48%	-3
6	09:00	5	3	1.59%	1.50%	-5.50%			09:00	5	3	1.59%	1.50%	-5.50%	0
7	09:30	14	12	4.44%	6.00%	35.00%			09:30	14	10	4.44%	5.00%	12.50%	-2
8	10:00	24	18	7.62%	9.00%	18.13%			10:00	24	18	7.62%	9.00%	18.13%	0
9	10:30	12	11	3.81%	5.50%	44.38%			10:30	12	9	3.81%	4.50%	18.13%	-2
10	11:00	28	26	8.89%	13.00%	46.25%			11:00	28	20	8.89%	10.00%	12.50%	-6
11	11:30	19	13	6.03%	6.50%	7.76%			11:30	19	14	6.03%	7.00%	16.05%	1
12	12:00	24	20	7.62%	10.00%	31.25%			12:00	24	18	7.62%	9.00%	18.13%	-2
13	12:30	17	8	5.40%	4.00%	-25.88%			12:30	17	9	5.40%	4.50%	-16.62%	1
14	13:00	46	20	14.60%	10.00%	-31.52%			13:00	46	24	14.60%	12.00%	-17.83%	4
15	13:30	45	20	14.29%	10.00%	-30.00%			13:30	45	24	14.29%	12.00%	-16.00%	4
16	14:00	45	20	14.29%	10.00%	-30.00%			14:00	45	24	14.29%	12.00%	-16.00%	4
17		315	200							315	200
Sheet1

Cell Formulas
Range	Formula
E4:E16,M4:M16	E4	=C4/$C$17
F4:F16,N4:N16	F4	=D4/$D$17
G4:G16,O4:O16	G4	=(F4-E4)/E4
P4:P16	P4	=L4-D4
C17:D17,K17:L17	C17	=SUM(C4:C16)

 

[Eric W]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														-20.00%	20.00%
2
3			Required	Available	Req %	Ava %	Ind %				Required	Available	Req %	Ava %	Ind %	Revised Numbers
4		0.333333	7	4	2.22%	2.00%	-10.00%			0.333333	7	5	2.22%	2.50%	12.50%	1	1
5		0.354167	29	25	9.21%	12.50%	35.78%			0.354167	29	22	9.21%	11.00%	19.48%	-3	3
6		0.375	5	3	1.59%	1.50%	-5.50%			0.375	5	3	1.59%	1.50%	-5.50%	0	0
7		0.395833	14	12	4.44%	6.00%	35.00%			0.395833	14	10	4.44%	5.00%	12.50%	-2	2
8		0.416667	24	18	7.62%	9.00%	18.13%			0.416667	24	18	7.62%	9.00%	18.13%	0	0
9		0.4375	12	11	3.81%	5.50%	44.38%			0.4375	12	9	3.81%	4.50%	18.13%	-2	2
10		0.458333	28	26	8.89%	13.00%	46.25%			0.458333	28	20	8.89%	10.00%	12.50%	-6	6
11		0.479167	19	13	6.03%	6.50%	7.76%			0.479167	19	14	6.03%	7.00%	16.05%	1	1
12		0.5	24	20	7.62%	10.00%	31.25%			0.5	24	18	7.62%	9.00%	18.13%	-2	2
13		0.520833	17	8	5.40%	4.00%	-25.88%			0.520833	17	9	5.40%	4.50%	-16.62%	1	1
14		0.541667	46	20	14.60%	10.00%	-31.52%			0.541667	46	24	14.60%	12.00%	-17.83%	4	4
15		0.5625	45	20	14.29%	10.00%	-30.00%			0.5625	45	24	14.29%	12.00%	-16.00%	4	4
16		0.583333	45	20	14.29%	10.00%	-30.00%			0.583333	45	24	14.29%	12.00%	-16.00%	4	4
17			315	200							315	200					30
Sheet4

Cell Formulas
Range	Formula
M4:M16,E4:E16	E4	=C4/$C$17
N4:N16,F4:F16	F4	=D4/$D$17
O4:O16,G4:G16	G4	=(F4-E4)/E4
P4:P16	P4	=L4-D4
Q17,K17:L17,C17:D17	C17	=SUM(C4:C16)
Q4:Q16	Q4	=ABS(P4)

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

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]

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]

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]

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]

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