Complex problem - how many widgets should I sell?

[GeekInPA]

Looking for some advice and formulas!

1. I've purchased many different colors of widgets.
2. For each color, I purchased it at one or multiple costs and it has either increased or decreased in value.
3. I'd like to sell different colors and quantities of my widgets and net out a $1000 total.
4. Ideally, I'd have a cell where I could replace $1000 with whatever value I'm trying to solve for.
5. I have the color name of the widget in column C. Red widget, Maroon widget, Scarlet widget, etc.
6. Starting at the red widget, I have the quantity of my red widgets in F2-6, followed by the quantity of Maroon widgets in F7, and so on
7. I have the cost I purchased that lot of red widgets for in G2, followed by the cost I purchased the second lot of red widgets in G3 each Maroon widget in G7, and so on
8. I have the total cost for that lot of Red widgets in H2, followed by the cost I purchased each Maroon widget for in H7
9. I have today's total market value of my red widgets in I2, and today's total market value of my maroon widgets in I7, and so on.
10. I have the total dollar value that my red widget lots have increased or decreased (negative number) in value in L2, and the total dollar value that my maroon lot widgets have increased or decreased in value in L7

Question: How can I determine which widgets I should sell and how many of those widgets I should sell so that I get $1000 and their sum increase/decrease is 0.
I have to sell them in integers and if it needs to be either or positive or negative and almost $1000, round to the negative.

Row	C:Description	F:Quantity	G: Cost per widget	H: Total cost	I: Current Total Market value	L: Total Current gain loss
2	Red	121	165.42	20015.82	21013.47	997.65
3	Red	0.195	142.72	27.83	33.86	6.03
4	Red	0.164	169.94	27.87	28.48	0.61
5	Red	0.197	141.68	27.91	34.21	6.3
6	Red	0.182	153.63	27.96	31.61	3.65
7	Maroon	4	50.02	200.08	222.28	22.2
8	Scarlet	40	153.27	6130.84	4733.2	-1397.64
9	Brick Red	1	386.54	386.54	336.15	-50.39
10	English Vermilion	6	178.42	1070.52	1269.78	199.26
11	Madder Lake	20	11.89	237.7	179.8	-57.9
12	Permanent Geranium Lake	3	318.18	954.54	1408.59	454.05
13	Maximum Red	1	63.45	63.45	62.16	-1.29
14	Maximum Red	0.003	63.33	0.19	0.19	0
15	Maximum Red	0.004	57.5	0.23	0.25	0.02
16	Chestnut	10	9.58	95.8	84.9	-10.9
17	Orange-Red	100	128.12	12812.1	12250	-562.1
18	Sunset Orange	1	33.71	33.71	37.13	3.42
19	Bittersweet	1	18.92	18.92	18.74	-0.18
20	Bittersweet	0.002	20	0.04	0.04	0
21	Dark Venetian Red	1	171.31	171.31	158.38	-12.93
22	Dark Venetian Red	0.007	161.43	1.13	1.11	-0.02
23	Dark Venetian Red	2	174.02	348.03	316.76	-31.27
24	Dark Venetian Red	0.019	178.95	3.4	3.01	-0.39
25	Dark Venetian Red	0.022	155.45	3.42	3.48	0.06
26	Venetian Red	5	70.5	352.5	344.75	-7.75
27	Venetian Red	0.04	73.75	2.95	2.76	-0.19
28	Venetian Red	0.046	64.57	2.97	3.17	0.2
29	Light Vanetian Red	5	37.57	187.84	192.25	4.41
30	Light Vanetian Red	0.049	40.82	2	1.88	-0.12
31	Light Vanetian Red	0.052	38.85	2.02	2	-0.02
32	Vivid Tangerine	1	54.65	54.65	62.89	8.24
33	Middle Red	0.007	62.86	0.44	0.44	0
34	Burnt Orange	0.007	65.71	0.46	0.44	-0.02
35	Red-Orange	10	8.47	84.65	80.6	-4.05
36	Orange	10	204.42	2044.2	2073.2	29
37	Orange	0.121	190.91	23.1	25.09	1.99
38	Orange	0.055	193.27	10.63	11.4	0.77
39	Orange	0.05	213.6	10.68	10.37	-0.31
40	Orange	0.052	206.54	10.74	10.78	0.04
41	Macaroni and Cheese	10	11.03	110.3	84.27	-26.03
42	Middle Yellow Red	4	250.42	1001.68	1172.76	171.08
43	Middle Yellow Red	0.023	240	5.52	6.74	1.22
44	Middle Yellow Red	0.022	252.27	5.55	6.45	0.9
45	Middle Yellow Red	0.022	279.55	6.15	6.45	0.3
46	Middle Yellow Red	0.023	268.7	6.18	6.74	0.56
47	Banana Mania	5	200.42	1002.1	1216.18	214.08
48	Maize	11	9.09	99.99	104.06	4.07
49	Orange-Yellow	5	221.42	1107.1	1823.89	716.79
50	Goldenrod	30	5.3	159	120.3	-38.7
51	Goldenrod	0.079	4.94	0.39	0.32	-0.07
52	Goldenrod	0.073	5.21	0.38	0.29	-0.09
53	Goldenrod	0.08	4.5	0.36	0.32	-0.04
54	Goldenrod	0.088	4.66	0.41	0.35	-0.06
55	Goldenrod	0.154	4.09	0.63	0.62	-0.01
56	Dandelion	1	71	71	60.44	-10.56
57	Dandelion	0.003	83.33	0.25	0.18	-0.07
58	Dandelion	0.004	62.5	0.25	0.24	-0.01
59	Dandelion	0.004	62.5	0.25	0.24	-0.01
60	Dandelion	12	62.27	747.18	725.28	-21.9
61	Dandelion	0.05	64.6	3.23	3.02	-0.21
62	Dandelion	0.05	64.8	3.24	3.02	-0.22
63	Dandelion	0.049	66.53	3.26	2.96	-0.3
64	Dandelion	0.05	65.4	3.27	3.02	-0.25
65	Dandelion	0.053	63.4	3.36	3.2	-0.16
66	Dandelion	0.055	61.45	3.38	3.32	-0.06
67	Dandelion	0.055	61.82	3.4	3.32	-0.08
68	Yellow	5	32.85	164.25	123.5	-40.75
69	Green-Yellow	2	27.69	55.37	54.5	-0.87
70	Green-Yellow	0.015	30	0.45	0.41	-0.04
71	Green-Yellow	0.017	28.24	0.48	0.46	-0.02
72	Middle Yellow	10	92.42	924.2	614.15	-310.05
73	Olive Green	2	121.56	243.12	211.62	-31.5
74	Olive Green	0.012	125	1.5	1.27	-0.23
75	Olive Green	0.012	125.83	1.51	1.27	-0.24
76	Spring Green	50	2.22	110.79	53	-57.79
77	Maximum Yellow	1	84.16	84.16	106.2	22.04
78	Maximum Yellow	0.006	81.67	0.49	0.64	0.15
79	Maximum Yellow	5	85.99	429.93	530.98	101.05
80	Maximum Yellow	3	85.97	257.91	318.59	60.68
81	Maximum Yellow	0.048	99.38	4.77	5.1	0.33
82	Maximum Yellow	0.047	102.13	4.8	4.99	0.19
83	Canary	1	46.51	46.51	49.17	2.66
84	Canary	0.005	42	0.21	0.25	0.04
85	Canary	0.004	45	0.18	0.2	0.02
86	Lemon Yellow	5	248.42	1242.1	789.57	-452.53
87	Lemon Yellow	0.03	150	4.5	4.74	0.24
88	Lemon Yellow	0.031	175.16	5.43	4.9	-0.53
89	Lemon Yellow	6	167.75	1006.47	947.48	-58.99
90	Lemon Yellow	0.079	151.27	11.95	12.48	0.53
91	Lemon Yellow	0.075	160.4	12.03	11.84	-0.19
92	Maximum Green Yellow	6	129.31	775.86	838.11	62.25
93	Maximum Green Yellow	63	328.47	20693.82	11032.88	-9660.95
94	Maximum Green Yellow	3	286.38	859.13	525.38	-333.76
95	Middle Green Yellow	4	28.51	114.04	156.24	42.2
96	Inchworm	2	46.84	93.68	106.03	12.35
97	Inchworm	0.019	45.26	0.86	1.01	0.15
98	Inchworm	0.017	49.41	0.84	0.9	0.06
99	Inchworm	0.018	51.11	0.92	0.95	0.03
100	Light Chrome Green	2	16.63	33.26	27.87	-5.39
101	Light Chrome Green	0.061	12.79	0.78	0.85	0.07
102	Light Chrome Green	0.039	12.82	0.5	0.54	0.04
103	Light Chrome Green	0.047	15.53	0.73	0.65	-0.08
104	Light Chrome Green	0.006	16.67	0.1	0.08	-0.02
105	Yellow-Green	1	76.11	76.11	76.51	0.4
106	Yellow-Green	0.004	72.5	0.29	0.31	0.02
107	Yellow-Green	0.004	75	0.3	0.31	0.01
108	Yellow-Green	0.004	77.5	0.31	0.31	0
109	Yellow-Green	0.004	72.5	0.29	0.31	0.02
110	Yellow-Green	0.004	77.5	0.31	0.31	0
111	Yellow-Green	0.004	77.5	0.31	0.31	0
112	Maximum Green	1509.299	34.94	52734.95	38306.01	-14428.9
113	Asparagus	71.522	44.14	3156.98	3019.66	-137.32
114	Granny Smith Apple	1	183.67	183.67	196.44	12.77
115	Granny Smith Apple	0.006	201.67	1.21	1.18	-0.03
116	Granny Smith Apple	0.007	165.71	1.16	1.38	0.22
117	Granny Smith Apple	0.008	182.5	1.46	1.57	0.11
118	Granny Smith Apple	0.004	215	0.86	0.79	-0.07
119	Fern	71	112.42	7981.82	7659.48	-322.34
120	Fern	0.366	113.09	41.39	39.48	-1.91
121	Middle Green	10	108.42	1084.2	939.6	-144.6
122	Middle Green	0.055	91.64	5.04	5.17	0.13
123	Middle Green	0.065	88	5.72	6.11	0.39
124	Middle Green	0.069	99.13	6.84	6.48	-0.36
125	Middle Green	0.071	94.79	6.73	6.67	-0.06
126	Green	93.856	28.92	2714.74	2425.24	-289.5
127	Medium Chrome Green	167.807	33	5537.86	5233.9	-303.96
128	Forest Green	71.045	124.38	8836.67	9412.75	576.08
129	Sea Green	1	393.42	393.42	382.96	-10.46
130	Sea Green	0.004	357.5	1.43	1.53	0.1
131	Sea Green	1	366.76	366.76	382.96	16.2
132	Sea Green	0.009	326.67	2.94	3.45	0.51
133	Sea Green	0.01	337	3.37	3.83	0.46
134	Sea Green	0.008	376.25	3.01	3.06	0.05
135	Shamrock	1	276.77	276.77	236.7	-40.07
136	Shamrock	50	262.42	13121	11835	-1286
137	Shamrock	0.1	227.2	22.72	23.67	0.95
138	Shamrock	0.122	210.82	25.72	28.88	3.16
139	Shamrock	0.142	210.92	29.95	33.61	3.66
140	Shamrock	0.156	225.06	35.11	36.93	1.82
141	Mountain Meadow	65.132	94.72	6169.24	6536	366.76
142	Jungle Green	1	224.61	224.61	259.56	34.95
143	Jungle Green	0.002	225	0.45	0.52	0.07
144	Jungle Green	0.2334	214.22	50	60.58	10.58
145	Jungle Green	0.002	265	0.53	0.52	-0.01
146	Caribbean Green	7	158.42	1108.94	1067.92	-41.02
147	Caribbean Green	0.031	126.45	3.92	4.73	0.81
148	Caribbean Green	0.03	131.33	3.94	4.58	0.64
149	Caribbean Green	0.028	141.07	3.95	4.27	0.32
150	Caribbean Green	0.027	149.63	4.04	4.12	0.08
151	Tropical Rain Forrest	213	92.09	19614.32	22365	2750.68
152	Tropical Rain Forrest	1.945	96.37	187.44	204.23	16.79
153	Tropical Rain Forrest	1.87	104.6	195.6	196.35	0.75
154	Tropical Rain Forrest	1.804	109.37	197.3	189.42	-7.88

 

[Dan_W]

This has all the hallmarks of a school assignment.

 

[GeekInPA]

This has all the hallmarks of a school assignment.

Nope. No school would be creative enough to have the problems I have in real life! lol The names in my problem were changed to protect the innocent!

 

[Dan_W]

"the innocent"... LOL....
I wouldn't be surprised if the schools trawl forums like these, and plagiarise questions like yours for materials... because, ya know, why not?
So don't be surprised if you learn your query is being used to torture business school students for years to come!

 

[GeekInPA]

"the innocent"... LOL....
I wouldn't be surprised if the schools trawl forums like these, and plagiarise questions like yours for materials... because, ya know, why not?
So don't be surprised if you learn your query is being used to torture business school students for years to come!

Got any suggestions to help solve it?

 

[Dan_W]

Hmm - well I was thinking about it, but am somewhat held back by the lack of a Windows laptop with Excel on it (it's in 'hospital' getting fixed).
My default solution for all things (rightly or wrongly) is to use the programming language in Excel called VBA - but I'm sensing you're after a formula based solution?

 

[GeekInPA]

I am open to learning how to use VBA if there is a solution. What are you thinking?

 

[Dan_W]

Well, it's Friday, so frankly, "thinking" is a bit of struggle at the moment - just trying to wrap my head around the task.
I'll ponder it some more, but I'm wondering actually if this is a good candidate for the What-If analysis function in Excel...
So presumably the goal is to sell all the widgets in different groupings of configurations. Can you set out a sample of what kind of output you would hope for (e.g., which color? how many of that colour? etc)

 

[AhoyNC]

You might want to look at Solver.
If Solver is not showing on your data ribbon then the link below will show how to add it plus give some examples.

These links give other solver examples show how to maximize profits that may help you figure out the number of widgets to sell.

 

[Dan_W]

You might want to look at Solver.
If Solver is not showing on your data ribbon then the link below will show how to add it plus give some examples.

These links give other solver examples show how to maximize profits that may help you figure out the number of widgets to sell.

Thank you! That's what I was thinking of when I said What If analysis!