Please help me get rid of the negative percentages.

[Tythesly]

I am trying to make a excel chart for Car Mechanic 2018 that gives random info that can help people with achievements. However I am having some... very annoying issues with this one table. Basically there is an achievement for spending cash in the store. But there are skills that give you a discount. Detailed Car Information.xlsx

If you could go to the DC Typhoon sheet and look to the right you will see a table that shows progression towards these achievements based on what level of discount they have.... The bronze side works perfectly... The silver and gold.... gives negative percentages and I don't know why.

Detailed Car Information.xlsx
	A	B	C	D	E	F	G	H	I
1	Part Name	Price	Number of Bolts	Examinable?		Achievement	Bronze	Silver	Gold
2	Alternator	$300	2			Wrench master	0.20%	0.00%	0.00%
3	Battery					Explorer	0.00%	0.00%	0.00%
4	Bottom Suspension Arm					Old Car?
5	Bottom Suspension Arm
6	Brake Caliper					I can afford that Progression Totals
7	Brake Caliper					Level of Discount	Bronze	Silver	Gold
8	Brake Caliper Cylinder					No Discount	88.90%	8.89%	0.89%
9	Brake Caliper Cylinder					5% Discount	83.90%	3.89%	-4.11%
10	Brake Disc Ventilated					10% Discount	78.90%	-1.11%	-9.11%
11	Brake Disc Ventilated					15% Discount	73.90%	-6.11%	-14.11%
12	Brake Drum
13	Brake Drum
14	Brake Pads
15	Brake Pads
16	Brake Servo
17	Brake Shoe
18	Brake Shoe
19	Cam Gear (V8 OHV)
20	Camshaft (V8 OHV)	$550
21	Carburetor (4-barrel)	$375
22	Carburetor (4-barrel)	$375
23	Clip B
24	Clip B
25	Clutch Plate	$350
26	Clutch Pressure Plate	$180
27	Clutch Release Bearing
28	Crankshaft (V8 OHV)
29	Crankshaft Bearing Cap
30	Crankshaft Bearing Cap
31	Crankshaft Bearing Cap
32	Crankshaft Pulley (V8 OHV)
33	DC Typhoon Front Bumper B	$700	0	No
34	DC Typhoon Front Left Door		0	No
35	DC Typhoon Front Left Door Window		0	No
36	DC Typhoon Front Right Door		0	No
37	DC Typhoon Front Right Door Window		0	No
38	DC Typhoon Front Window		0	No
39	DC Typhoon Hood B	$700	0	No
40	DC Typhoon Left Headlight B	$500	0	No
41	DC Typhoon Left Side Mirror		0	No
42	DC Typhoon Left Taillight B	$350	0	No
43	DC Typhoon Rear Bumper B	$700	0	No
44	DC Typhoon Right Headlight B	$500	0	No
45	DC Typhoon Right Side Mirror		0	No
46	DC Typhoon Right Taillight B	$350	0	No
47	DC Typhoon Trunk		0	No
48	Double Wishbone Shock Absorber
49	Double Wishbone Shock Absorber
50	Drive Shaft
51	Drum Wheel Cylinder
52	Drum Wheel Cylinder
53	Engine Block (V8 OHV)
54	Engine Head (V8 OHV)	$800
55	Engine Head (V8 OHV)	$800
56	Engine Head Cover A (V8 OHV)
57	Engine Head Cover B (V8 OHV)
58	Exhaust Manifold (V8 OHV)	$160
59	Exhaust Manifold (V8 OHV)	$160
60	Flywheel	$650
61	Front Axle Knuckle Cover
62	Front Axle Knuckle Cover
63	Front license Plate		0	No
64	Front Shock Absorber Cap
65	Front Shock Absorber Cap
66	Front Spring
67	Front Spring
68	Front Steering Knuckle C
69	Front Steering Knuckle C
70	Front Suspension Crossmember D
71	Front Sway Bar B
72	Front Wheel Hub
73	Front Wheel Hub
74	Fuel Filter	$90
75	Fuel Pump	$300
76	Fuel Tank
77	Gearbox (V8 OHV)
78	Ignition Coil B
79	Ignition Distributor
80	Ignition Distributor Cap
81	Ignition Distributor Rotor
82	Ignition Wires (V8)
83	Inner Tie Rod
84	Inner Tie Rod
85	Intake Manifold (2 carb)
86	Leaf Spring
87	Leaf Spring
88	Leaf Spring Plate
89	Leaf Spring Plate
90	Leaf Spring U-Bolt
91	Leaf Spring U-Bolt
92	Leaf Spring U-Bolt
93	Leaf Spring U-Bolt
94	Leaf-Spring Rear Drive Axle
95	Oil Filter (V8 OHV)
96	Oil Pan (V8 OHV)
97	Outer Tie Rod
98	Outer Tie Rod
99	Piston Rings
100	Piston Rings
101	Piston Rings
102	Piston Rings
103	Piston Rings
104	Piston Rings
105	Piston Rings
106	Piston Rings
107	Piston with Conrod
108	Piston with Conrod
109	Piston with Conrod
110	Piston with Conrod
111	Piston with Conrod
112	Piston with Conrod
113	Piston with Conrod
114	Piston with Conrod
115	Power Steering Pump (V8)
116	Race Tire B
117	Race Tire B
118	Race Tire B
119	Race Tire B
120	Radiator C
121	Radiator Fan (V8 OHV)
122	Rear Axle Knuckle Housing B
123	Rear Axle Knuckle Housing B
124	Rear License Plate		0	No
125	Rear Muffler D
126	Rear Muffler D
127	Rear Shock Absorber B
128	Rear Shock Absorber B
129	Rim Typhoon
130	Rim Typhoon
131	Rim Typhoon
132	Rim Typhoon
133	Rocker Arm
134	Rocker Arm
135	Rocker Arm
136	Rocker Arm
137	Rocker Arm
138	Rocker Arm
139	Rocker Arm
140	Rocker Arm
141	Rocker Arm
142	Rocker Arm
143	Rocker Arm
144	Rocker Arm
145	Rocker Arm
146	Rocker Arm
147	Rocker Arm
148	Rocker Arm
149	Rod Cap
150	Rod Cap
151	Rod Cap
152	Rod Cap
153	Rod Cap
154	Rod Cap
155	Rod Cap
156	Round Air Filter (2 carb)
157	Round Air Filter Base (2 carb)
158	Round Air Filter Cover (2 carb)
159	Rubber Bushing
160	Rubber Bushing
161	Rubber Bushing
162	Rubber Bushing
163	Rubber Bushing
164	Rubber Bushing
165	Rubber Bushing
166	Rubber Bushing
167	Rubber Bushing
168	Rubber Bushing
169	Rubber Bushing
170	Rubber Bushing
171	Seat Typhoon
172	Seat Typhoon
173	Serpentine Belt A (V8 OHV)
174	Serpentine Belt B (V8 OHV)
175	Small Rubber Bushing
176	Small Rubber Bushing
177	Small Rubber Bushing
178	Small Rubber Bushing
179	Solid Rear Drive Axle
180	Solid Rear Drive Axle
181	Spark Plug
182	Spark Plug
183	Spark Plug
184	Spark Plug
185	Spark Plug
186	Spark Plug
187	Spark Plug
188	Spark Plug
189	Starter (V8)
190	Steering Rack
191	Steering Wheel Typhoon
192	Sway Bar Front End Link B
193	Sway Bar Front End Link B
194	Timing Chain (V8 OHV)
195	Timing Cover (V8 OHV)
196	Upper Suspension Arm
197	Upper Suspension Arm
198	Valve Push Rod
199	Valve Push Rod
200	Valve Push Rod
201	Valve Push Rod
202	Valve Push Rod
203	Valve Push Rod
204	Valve Push Rod
205	Valve Push Rod
206	Valve Push Rod
207	Valve Push Rod
208	Valve Push Rod
209	Valve Push Rod
210	Valve Push Rod
211	Valve Push Rod
212	Valve Push Rod
213	Valve Push Rod
214	Water Pump (V8 OHV)
215	Water Pump Pulley
216	Wheel Hub 4
217	Wheel Hub 4
218	Wheel Hub Bearing
219	Wheel Hub Bearing
220	Wheel Hub Cap
221	Wheel Hub Cap
222		$8,890.00	2	0
DC Typhoon

Cell Formulas
Range	Formula
G2	G2	=Table14[[#Totals],[Number of Bolts]]/1000
H2	H2	=Table14[[#Totals],[Number of Bolts]]/100000
I2	I2	=Table14[[#Totals],[Number of Bolts]]/500000
G3	G3	=Table14[[#Totals],[Examinable?]]/250
H3	H3	=Table14[[#Totals],[Examinable?]]/1000
I3	I3	=Table14[[#Totals],[Examinable?]]/10000
G8	G8	=Table14[[#Totals],[Price]]/10000
H8	H8	=Table14[[#Totals],[Price]]/100000
I8	I8	=Table14[[#Totals],[Price]]/1000000
G9	G9	=(Table14[[#Totals],[Price]]/10000)-5%
H9	H9	=(Table14[[#Totals],[Price]]/100000)-5%
I9	I9	=(Table14[[#Totals],[Price]]/1000000)-5%
G10	G10	=(Table14[[#Totals],[Price]]/10000)-10%
H10	H10	=(Table14[[#Totals],[Price]]/100000)-10%
I10	I10	=(Table14[[#Totals],[Price]]/1000000)-10%
G11	G11	=(Table14[[#Totals],[Price]]/10000)-15%
H11	H11	=(Table14[[#Totals],[Price]]/100000)-15%
I11	I11	=(Table14[[#Totals],[Price]]/1000000)-15%
B222	B222	=SUBTOTAL(109,[Price])
C222	C222	=SUBTOTAL(109,[Number of Bolts])
D222	D222	=COUNTIF([Examinable?],"yes")

 

[Tythesly]

Its supposed to show the percentage gained from spending the cash however the higher the discount rate skill you have the less it would progress. Bronze is $10,000, Silver is $100,000, and Gold is $1.000,000. I tried adding more decimal spaces but it just added zeros. IF I am doing my math right (my math skills are not the best) it should be something like .0076 % for gold but it shows -14.11%.

 

[StephenCrump]

Welcome to the Forum!

It's not totally clear what you're trying to do, but I think the problem distils down to:

$X less a y% discount is not $X - y, but rather (1-y) x $X.

	A	B	C	D
1	Amount	$8,890
2
3	You have ...
4	Level of Discount	Bronze	Silver	Gold
5	No Discount	88.90%	8.89%	0.89%
6	5% Discount	83.90%	3.89%	-4.11%
7	10% Discount	78.90%	-1.11%	-9.11%
8	15% Discount	73.90%	-6.11%	-14.11%
9
10	Perhaps you want ...
11		10,000	100,000	1,000,000
12	Level of Discount	Bronze	Silver	Gold
13	0%	88.90%	8.89%	0.89%
14	5%	84.46%	8.45%	0.84%
15	10%	80.01%	8.00%	0.80%
16	20%	71.12%	7.11%	0.71%
Sheet1

Cell Formulas
Range	Formula
B5	B5	=Amount/10000
C5	C5	=Amount/100000
D5	D5	=Amount/1000000
B6	B6	=Amount/10000-5%
C6	C6	=Amount/100000-5%
D6	D6	=Amount/1000000-5%
B7	B7	=Amount/10000-10%
C7	C7	=Amount/100000-10%
D7	D7	=Amount/1000000-10%
B8	B8	=Amount/10000-15%
C8	C8	=Amount/100000-15%
D8	D8	=Amount/1000000-15%
B13:D16	B13	=Amount/B$11*(1-$A13)

Named Ranges
Name	Refers To	Cells
Amount	=Sheet1!$B$1	B5:D8, B13:D16

 

[Tythesly]

Welcome to the Forum!

It's not totally clear what you're trying to do, but I think the problem distils down to:

$X less a y% discount is not $X - y, but rather (1-y) x $X.

	A	B	C	D
1	Amount	$8,890
2
3	You have ...
4	Level of Discount	Bronze	Silver	Gold
5	No Discount	88.90%	8.89%	0.89%
6	5% Discount	83.90%	3.89%	-4.11%
7	10% Discount	78.90%	-1.11%	-9.11%
8	15% Discount	73.90%	-6.11%	-14.11%
9
10	Perhaps you want ...
11		10,000	100,000	1,000,000
12	Level of Discount	Bronze	Silver	Gold
13	0%	88.90%	8.89%	0.89%
14	5%	84.46%	8.45%	0.84%
15	10%	80.01%	8.00%	0.80%
16	20%	71.12%	7.11%	0.71%
Sheet1

Cell Formulas
Range	Formula
B5	B5	=Amount/10000
C5	C5	=Amount/100000
D5	D5	=Amount/1000000
B6	B6	=Amount/10000-5%
C6	C6	=Amount/100000-5%
D6	D6	=Amount/1000000-5%
B7	B7	=Amount/10000-10%
C7	C7	=Amount/100000-10%
D7	D7	=Amount/1000000-10%
B8	B8	=Amount/10000-15%
C8	C8	=Amount/100000-15%
D8	D8	=Amount/1000000-15%
B13:D16	B13	=Amount/B$11*(1-$A13)

Named Ranges
Name	Refers To	Cells
Amount	=Sheet1!$B$1	B5:D8, B13:D16

THANK YOU!!!! I was so confused and now I learned something new haha. I never tried to make a sheet like this before so I appreciate the help. This is exactly what i was looking for :D

 

[StephenCrump]

Great, I'm glad it worked for you. Thanks for posting back.