I am attempting to set up spreadsheet for a homework problem involving Excel Solver. In the problem, there are a minimum number of parts to be built which can be built in house or outsourced. Parts built in house have variable costs(expressed under the in-house cost and purchase cost fields in the spreadsheet) and fixed setup costs which only occur in the event that 1 or more parts in a particular category are built in-house. Parts outsourced have variable costs based on number produced/purchased as parts produced in house do, but do not have setup costs. Each part produced in-house also takes a certain amount of time to make, and the in-house production facilities have a finite amount of time to devote to the parts.

I am able to set the problem up just fine when only considering the variable costs, but I'm running into trouble when trying to add in the fixed costs. I tried setting it up with an "if" statement which will return the fixed costs if the production amounts > 0 = True and return 0 if production costs > 0 = false, and then setting up the objective cell to add in the sum of the returned values. When I add this, however, Solver gives me a "Linearity conditions not satisfied" error.

Again, when the fixed costs are not considered I do not have this error. I have no idea how to set it up to conditionally include these costs otherwise, so I'm a little lost. Any ideas?

1 Subassembly 2 Plastic Base 3 Screen Controller 4 LED Screen 5 Keyboard Controller 6 Numerical Keypad Purchase Cost 0.65 3.45 0.5 3.7 0.7 Set up 1000 1200 1500 1900 1500 In-House Time 0.9 2.2 0.8 Number Built 12000 6184 Number Bought 0 816 6 In-House Cost 0.4 2.9 Plastic Base Screen Controller LED Screen Keyboard Controller Numerical Keypad 3.15 0.55 0 8 Price In-House 9 Plastic Base 10 Screen Controller 11 LED Screen 12 Keyboard Controller 13 Numerical Keypad Time H2 D2 -SUM(B9:B13) Total Actual Setup IF(H2 >0,B2,0) IF(H3>0,B3,0) IF(H4>0,B4,0) IF(H5>0,B5,0) F(H6> 0,B6,0) isM(E15:E19) Total Cost #814 +B21-E20 15 Price Bought 16 Plastic Base 17 Screen Controller 18 LED Screen 19 Keyboard Controller 20 Numerical Keypad 21 =E4 ?4 total - SUM(B16:B20) Total 1 Subassembly 2 Plastic Base 3 Screen Controller 4 LED Screen 5 Keyboard Controller 6 Numerical Keypad Purchase Cost 0.65 3.45 0.5 3.7 0.7 Set up 1000 1200 1500 1900 1500 In-House Time 0.9 2.2 0.8 Number Built 12000 6184 Number Bought 0 816 6 In-House Cost 0.4 2.9 Plastic Base Screen Controller LED Screen Keyboard Controller Numerical Keypad 3.15 0.55 0 8 Price In-House 9 Plastic Base 10 Screen Controller 11 LED Screen 12 Keyboard Controller 13 Numerical Keypad Time H2 D2 -SUM(B9:B13) Total Actual Setup IF(H2 >0,B2,0) IF(H3>0,B3,0) IF(H4>0,B4,0) IF(H5>0,B5,0) F(H6> 0,B6,0) isM(E15:E19) Total Cost #814 +B21-E20 15 Price Bought 16 Plastic Base 17 Screen Controller 18 LED Screen 19 Keyboard Controller 20 Numerical Keypad 21 =E4 ?4 total - SUM(B16:B20) Total