Increase by n until formula works

[Pestomania]

Below is a table which I am trying to identify formulas which would identify if current capacity is less than total hours needed, it changes implement 3rd shift to (1) and then increases proposed equipment by (+n) until proposed capacity is greater than total hrs needed. I have filled in Sept 23 through Dec 23 as an example. I left MAy 23 to Aug 23 for your purpose.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		23-May	23-Jun	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec
2	Number of Equipment	1	1	1	1	1	2	2	2		This is a static value
3	Number of hrs per Equipment	300	300	300	300	300	300	300	300		This is a static value
4	Utilization Rate	90%	90%	90%	90%	90%	90%	90%	90%		This is a static value
5	Program 1 (hrs required)	100	100	300	300	300	300	300	300
6	Program 2 (hrs required)	200	200	400	400	900	1200	1800	2200
7	Total Hrs needed	300	300	700	700	1200	1500	2100	2500
8	Current Capacity	270	270	270	270	270	540	540	540
9	Proposed Capacity	270	270	270	270	1296	1728	2160	2592
10	Proposed Equipment	0	0	0	0	2	2	3	4		if current capacity is less than total hours needed, it changes implement 3rd shift to (1) and then increases proposed equipment by (+n) until proposed capacity is greater than total hrs needed.
11	Implement 3rd Shift	0	0	0	0	1	1	1	1		This field should change to 1 before proposed equipment
Sheet1

Cell Formulas
Range	Formula
B7:I7	B7	=SUM(B5:B6)
B8:I8	B8	=B2*B3*B4
B9:I9	B9	=B4*IF(B11=1,480,B3)*(B2+B10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9:I9	Cell Value	<B$7	text	NO

 

[kevin9999]

Try the following:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		23-May	23-Jun	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec
2	Number of Equipment	1	1	1	1	1	2	2	2		This is a static value
3	Number of hrs per Equipment	300	300	300	300	300	300	300	300		This is a static value
4	Utilization Rate	90%	90%	90%	90%	90%	90%	90%	90%		This is a static value
5	Program 1 (hrs required)	100	100	300	300	300	300	300	300
6	Program 2 (hrs required)	200	200	400	400	900	1200	1800	2200
7	Total Hrs needed	300	300	700	700	1200	1500	2100	2500
8	Current Capacity	270	270	270	270	270	540	540	540
9	Proposed Capacity	432	432	864	864	1296	1728	2160	2592
10	Proposed Equipment	0	0	1	1	2	2	3	4		if current capacity is less than total hours needed, it changes implement 3rd shift to (1) and then increases proposed equipment by (+n) until proposed capacity is greater than total hrs needed.
11	Implement 3rd Shift	1	1	1	1	1	1	1	1		This field should change to 1 before proposed equipment
Sheet1

Cell Formulas
Range	Formula
B7:I7	B7	=SUM(B5:B6)
B8:I8	B8	=B2*B3*B4
B9:I9	B9	=CEILING(B7,B4*IF(B11=1,480,B3))
B10:I10	B10	=B9/(B4*IF(B11=1,480,B3))-B2
B11:I11	B11	=IF(B8<B7,1,0)

 

[Pestomania]

Try the following:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		23-May	23-Jun	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec
2	Number of Equipment	1	1	1	1	1	2	2	2		This is a static value
3	Number of hrs per Equipment	300	300	300	300	300	300	300	300		This is a static value
4	Utilization Rate	90%	90%	90%	90%	90%	90%	90%	90%		This is a static value
5	Program 1 (hrs required)	100	100	300	300	300	300	300	300
6	Program 2 (hrs required)	200	200	400	400	900	1200	1800	2200
7	Total Hrs needed	300	300	700	700	1200	500	2100	2500
8	Current Capacity	270	270	270	270	270	540	540	540
9	Proposed Capacity	432	432	864	864	1296	1728	2160	2592
10	Proposed Equipment	0	0	1	1	2	2	3	4		if current capacity is less than total hours needed, it changes implement 3rd shift to (1) and then increases proposed equipment by (+n) until proposed capacity is greater than total hrs needed.
11	Implement 3rd Shift	1	1	1	1	1	1	1	1		This field should change to 1 before proposed equipment
Sheet1

Cell Formulas
Range	Formula
B7:I7	B7	=SUM(B5:B6)
B8:I8	B8	=B2*B3*B4
B9:I9	B9	=CEILING(B7,B4*IF(B11=1,480,B3))
B10:I10	B10	=B9/(B4*IF(B11=1,480,B3))-B2
B11:I11	B11	=IF(B8<B7,1,0)

This works exactly as I requested it to, but I forgot one important part.

If the field to the left is a number, the minimum number is that. In G6, the demand dropped for that month, which changes proposed equipment back to 1. Since we aren't going to remove equipment bakc and forth due to drop in demand, we would need to maintain the fact that (2) machines are installed at that point.

I hope this makes sense.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		23-May	23-Jun	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec
2	Number of Equipment	1	1	1	1	1	2	2	2		This is a static value
3	Number of hrs per Equipment	300	300	300	300	300	300	300	300		This is a static value
4	Utilization Rate	90%	90%	90%	90%	90%	90%	90%	90%		This is a static value
5	Program 1 (hrs required)	100	100	300	300	300	300	300	300
6	Program 2 (hrs required)	200	200	400	400	900	200	1800	2200
7	Total Hrs needed	300	300	700	700	1200	500	2100	2500
8	Current Capacity	270	270	270	270	270	540	540	540
9	Proposed Capacity	432	432	864	864	1296	1728	2160	2592
10	Proposed Equipment	0	0	1	1	2	1	3	4		if current capacity is less than total hours needed, it changes implement 3rd shift to (1) and then increases proposed equipment by (+n) until proposed capacity is greater than total hrs needed.
11	Implement 3rd Shift	1	1	1	1	1	1	1	1		This field should change to 1 before proposed equipment
Sheet1

Cell Formulas
Range	Formula
B7:I7	B7	=SUM(B5:B6)
B8:I8	B8	=B2*B3*B4
B9:I9	B9	=CEILING(B7,B4*IF(B11=1,480,B3))
B10:I10	B10	=B9/(B4*IF(B11=1,480,B3))-B2
B11:I11	B11	=IF(B8<B7,1,0)

 

[kevin9999]

There are some things I don't understand. Your logic in bothe your posts says that if the current capacity (row 8) is less than total hours needed (row 7) then Implement 3rd Shift becomes 1. In your example in post #3 your current capacity (row 8) is not less than total hours needed (row 7) - so why is it not showing 0? Do you want that Implement 3rd Shift figure in row 11 to remain at 1 if the cell to the left is already 1?
To accommodate the additional condition you've now introduced will take a great deal of thinking (it won't happen until tomorrow) and I'm not sure I can come up with a solution.

 

[Pestomania]

There are some things I don't understand. Your logic in bothe your posts says that if the current capacity (row 8) is less than total hours needed (row 7) then Implement 3rd Shift becomes 1. In your example in post #3 your current capacity (row 8) is not less than total hours needed (row 7) - so why is it not showing 0? Do you want that Implement 3rd Shift figure in row 11 to remain at 1 if the cell to the left is already 1?
To accommodate the additional condition you've now introduced will take a great deal of thinking (it won't happen until tomorrow) and I'm not sure I can come up with a solution.

Hi Kevin, yes. The concept being once a machine is installed or a shift is implemented, it will stay that way till the end of time.

 

[kevin9999]

This is about the best I can get it, taking your latest condition into account. If this doesn't give you what you want, I'm afraid I'll have to leave it to others to finalise your requirements.

MULTI on hold.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		23-May	23-Jun	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec
2	Number of Equipment	1	1	1	1	1	2	2	2		This is a static value
3	Number of hrs per Equipment	300	300	300	300	300	300	300	300		This is a static value
4	Utilization Rate	90%	90%	90%	90%	90%	90%	90%	90%		This is a static value
5	Program 1 (hrs required)	100	100	300	300	300	300	300	300
6	Program 2 (hrs required)	200	200	400	400	900	200	1800	2200
7	Total Hrs needed	300	300	700	700	1200	500	2100	2500
8	Current Capacity	270	270	270	270	270	540	540	540
9	Proposed Capacity	432	432	864	864	1296	864	2160	2592
10	Proposed Equipment	0	0	1	1	2	2	3	4		if current capacity is less than total hours needed, it changes implement 3rd shift to (1) and then increases proposed equipment by (+n) until proposed capacity is greater than total hrs needed.
11	Implement 3rd Shift	1	1	1	1	1	1	1	1		This field should change to 1 before proposed equipment
Sheet1

Cell Formulas
Range	Formula
B7:I7	B7	=SUM(B5:B6)
B8:I8	B8	=B2*B3*B4
B9:I9	B9	=CEILING(B7,B4*IF(B11=1,480,B3))
B10:I10	B10	=IF(AND(B9/(B4*IF(B11=1,480,B3))-B2=0,AND(ISNUMBER(A10),A10>0)),A10,B9/(B4*IF(B11=1,480,B3))-B2)
B11	B11	=IF(B8<B7,1,0)
C11:I11	C11	=IF(C8<C7,1,IF(B11>0,B11,0))

 

[Pestomania]

This is about the best I can get it, taking your latest condition into account. If this doesn't give you what you want, I'm afraid I'll have to leave it to others to finalise your requirements.

MULTI on hold.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		23-May	23-Jun	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec
2	Number of Equipment	1	1	1	1	1	2	2	2		This is a static value
3	Number of hrs per Equipment	300	300	300	300	300	300	300	300		This is a static value
4	Utilization Rate	90%	90%	90%	90%	90%	90%	90%	90%		This is a static value
5	Program 1 (hrs required)	100	100	300	300	300	300	300	300
6	Program 2 (hrs required)	200	200	400	400	900	200	1800	2200
7	Total Hrs needed	300	300	700	700	1200	500	2100	2500
8	Current Capacity	270	270	270	270	270	540	540	540
9	Proposed Capacity	432	432	864	864	1296	864	2160	2592
10	Proposed Equipment	0	0	1	1	2	2	3	4		if current capacity is less than total hours needed, it changes implement 3rd shift to (1) and then increases proposed equipment by (+n) until proposed capacity is greater than total hrs needed.
11	Implement 3rd Shift	1	1	1	1	1	1	1	1		This field should change to 1 before proposed equipment
Sheet1

Cell Formulas
Range	Formula
B7:I7	B7	=SUM(B5:B6)
B8:I8	B8	=B2*B3*B4
B9:I9	B9	=CEILING(B7,B4*IF(B11=1,480,B3))
B10:I10	B10	=IF(AND(B9/(B4*IF(B11=1,480,B3))-B2=0,AND(ISNUMBER(A10),A10>0)),A10,B9/(B4*IF(B11=1,480,B3))-B2)
B11	B11	=IF(B8<B7,1,0)
C11:I11	C11	=IF(C8<C7,1,IF(B11>0,B11,0))

Thank you! this seems to do the trick.

 

[kevin9999]

Glad it worked for you, and thanks for the feedback