Quantity needed to get to a specific number

[ACFTmech]

I hope this all makes sense. We need to log dimensions (Column Labeled A&B in BLUE.) to show if a blade requires replacement. These blades range in dimension.

The A blades range between 0.670 to 0.682 for used blades, and 0.683 for new blades.
The B blades range between 0.646 to 0.652 for used blades, and 0.653 for new blades.

My target Estimated Gap is 0.190 or below. So here is the issue:

The cost varies considerably on new vs used blades. Ideally used blades would be best to bring the Estimated Gap into an acceptable range. However, sometimes new blades will bring the Estimated Gap in tolerance with only a few new blades and therefore cost less for the customer. I would like a calculation of blades required for both new & used blades in separate cells. The only numbers that would be entered manually would be the columns in BLUE. I have included a sample chart of what I am picturing:

2ND PT ROTOR GAP CALCULATOR concept.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	2nd PT Circumferential check								SAMPLE of what I would like this chart to look like
2									Blades required to close gap
3	Blade #	A	B	Deviation from nominal (A blade)	Deviation from nominal (B blade)	Combined Circumference	ESTIMATED GAP		New A blades	New B blades	Est gap with new	Used A blades	Used B blades	Est gap with used
4	1	0.673	0.649	-0.005	-0.001	41.2135	0.2115		0.683	0.653	0.188	0.68	0.653	0.188
5	2	0.676	0.651	-0.002	0.001				0.683	0.651	Total new A&B	0.68	0.651	Total Used A&B
6	3	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
7	4	0.679	0.651	0.001	0.001				0.679	0.651	4	0.679	0.651	8
8	5	0.679	0.649	0.001	-0.001				0.679	0.649		0.679	0.65
9	6	0.676	0.649	-0.002	-0.001				0.676	0.649		0.679	0.65
10	7	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
11	8	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
12	9	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
13	10	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
14	11	0.676	0.651	-0.002	0.001				0.676	0.651		0.679	0.651
15	12	0.68	0.651	0.002	0.001				0.68	0.651		0.68	0.651
16	13	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
17	14	0.68	0.652	0.002	0.002				0.68	0.652		0.68	0.652
18	15	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
19	16	0.679	0.649	0.001	-0.001				0.679	0.649		0.679	0.649
20	17	0.679	0.65	0.0005	0				0.6785	0.65		0.6785	0.65
21	18	0.679	0.65	0.001	0				0.679	0.65		0.679	0.65
22	19	0.681	0.6505	0.003	0.0005				0.681	0.6505		0.681	0.6505
23	20	0.679	0.652	0.001	0.002				0.679	0.652		0.679	0.652
24	21	0.679	0.652	0.001	0.002				0.679	0.652		0.679	0.652
25	22	0.68	0.65	0.002	0				0.68	0.65		0.68	0.65
26	23	0.68	0.65	0.002	0				0.68	0.65		0.68	0.65
27	24	0.68	0.652	0.002	0.002				0.68	0.652		0.68	0.652
28	25	0.68	0.651	0.002	0.001				0.68	0.651		0.68	0.651
29	26	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
30	27	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
31	28	0.679	0.651	0.001	0.001				0.679	0.651		0.679	0.651
32	29	0.679	0.652	0.001	0.002				0.679	0.652		0.679	0.652
33	30	0.679	0.6505	0.0005	0.0005				0.6785	0.653		0.6785	0.651
34	31	0.679	0.6505	0.001	0.0005				0.679	0.6505		0.679	0.6505
35		TOTAL GAP TO CLOSE		-0.011	-0.004				Combined Circumfrence			Combined Circumfrence
36
37									41.237			41.237
Sheet1

Cell Formulas
Range	Formula
D4:D34	D4	=SUM(B4-0.678)
E4:E34	E4	=SUM(C4-0.65)
F4	F4	=SUM(B4:C34)
G4	G4	=SUM(41.425-F4)
K4,N4	K4	=SUM(41.425-I37)
D35:E35	D35	=SUMIF(D4:D34,"<0")
I37,L37	I37	=SUM(I4:J34)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N4	Cell Value	>0.21	text	NO
N4	Cell Value	<0.21	text	NO
K4	Cell Value	>0.21	text	NO
K4	Cell Value	<0.21	text	NO
E4:E35	Cell Value	>0	text	NO
E4:E35	Cell Value	<0	text	NO
D4:D35	Cell Value	>0	text	NO
D4:D35	Cell Value	<0	text	NO
G4	Cell Value	>0.21	text	NO
G4	Cell Value	<0.21	text	NO

 

[ACFTmech]

Am I asking the wrong question? Or is it not possible?

 

[mediumrare]

Am I asking the wrong question? Or is it not possible?

Could you explain Estimated Gap? And Combined Circumference?

 

[ACFTmech]

So this is on a rotor that has a total of 62 blades consisting of 31 A blades, and 31 B blades. A&B blades create a set. We have to insert shims between sets to check the Cumulative Gap, the Estimated Gap in the spreadsheet is a calculation of the rotor without gaps as if it were completely new (this circumference is 41.425"). The Combined Circumference is the width of all 31 A blades and 31 B blades. When the Combined Circumference is subtracted from the 41.425, we are left with the estimated gap. This gap needs to be corrected if it is above 0.210", when we correct it, we like it to be 0.190" or less.

New from the factory blades are wider than used replacement blades. Both can be used to close this gap. However, the customer needs to know how many new blades will be replaced vs. used blades to bring it in tolerance. It can be a significant cost difference.

 

[mediumrare]

And why 0.678 and 0.65? Versus 0.683 and 0.653?

When you say that you "would like a calculation of blades required for both new & used blades in separate cells," what all would that entail? Are you wanting it to estimate and calculate which blades can be used? How many shims? Why are there four and eight cells highlighted in the SAMPLE side?