{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Instructions","Optimizer","Report","Materials","HiddenSheet","Documentation"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":375,"comments":3,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Instructions","index":0,"n_rows":52,"n_cols":2,"rows_kept":52,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Instructions for Spartan Compost Recipe Optimizer"]
[null,"version 1.05  2018"]
[]
[null,"1.  Overview"]
[null,"Spartan Compost Recipe Optimizer is a Microsoft® Office Excel application that simplifies planning a compost recipe or mix.  The spreadsheet will work in the preparation of most compost mixes."]
[null,"The application uses Excel's Solver to automate the process of developing or formulating a compost recipe.  The amounts of materials to be used in a compost mix are determined based on meeting compost performance constraints (a.k.a. nutrient balance, compost characteristics, or compost conditions) while minimizing cost.  Material constraints and costs can be set to limit the amount of specific ingredients that are used in the mix.  Performance constraints or targets are set for percent moisture, carbon nitrogen ratio (C:N), and (or) bulk density. "]
[null,"Spartan Compost Recipe Optimizer was developed by Dr. Dale Rozeboom (Department of Animal Science, Michigan State University) and Robert Kriegel M.S. (ANR Technology Services, Michigan State University). Dean Ross (Agrosecurity Consulting) collaborated in revisions made in version 1.03."]
[null,"Copyright © 2013 Michigan State University Board of Trustees.  All Rights Reserved."]
[null,"The Excel workbook contains the following worksheets:"]
[null,"Instructions.  This sheet contains instructions for using Spartan Compost Recipe Optimizer."]
[null,"Optimizer.  Use this sheet to select materials used in the compost mix, set material and performance constraints, and run the Solver to optimize the mix.  Alternatively, the amounts of materials in a compost mix can also be entered manually."]
[null,"Report.  Use this sheet to print a batch report when preparing the compost recipe."]
[null,"Materials. This sheet contains a library of materials that can be used in a compost mix.  Normally, there will be no need to modify this sheet.  However, if the addition of new materials to the library or permanently change the composition of a material is desired, do so in this sheet.  The sheet contains instructions for modifying the materials library."]
[null,"HiddenSheet.  This sheet contains calculations and intermediates used by Excel's Solver to optimize the compost mix.  Do not change anything on this sheet.  This sheet is normally hidden from users."]
[null,"Documentation.  Sheet contains detailed technical documentation for the application.  This sheet will also be hidden from users.  Authors will print and archive this worksheet."]
[]
[null,"2.  System Requirements"]
[null,"Security - Excel 2003.  Due to changes in how Excel handles macros, there are separate versions of this application for Excel 2003 and for Excel 2007-2010.  You must have Microsoft Excel 2003 or later to run Spartan ATC Emergency Planner.  The spreadsheet contains macros, so MS Excel's security must be set to moderate or low to allow the macros to function.  To change the security setting select Tools > Macro > Security from Excel's main menu and set security to moderate or low.  Since macros are only loaded when a file is first opened, you need to close and reopen a file after changing Excel's macro security settings.  Depending on your security setting, Excel may ask you whether to enable or disable macros when you open the file.  Macros must be enabled for the application to work correctly. \n"]
[null,"Security - Excel 2007 and later. Due to changes in how Excel handles macros, there are separate versions of this application for Excel 2003 and for Excel 2007-2010.  You must have Microsoft Excel 2007 or later to run this version of Spartan ATC Emergency Planner. The spreadsheet contains macros, so MS Excel's security must be set to moderate or low to allow the macros to function.  To change the security setting select the Developer tab on Excel's main menu, then select 'Macro Security' from the toolbar and Tools > Macro > Security from Excel's main menu and set security to enable macros.  Depending on your security setting, Excel may ask you whether to enable or disable macros when you open the file.  Macros must be enabled for the application to work operate correctly.  "]
[null,"Solver - Excel 2003.  The Optimizer Worksheet also requires the Solver Add-in that is included with MS Excel.  Solver is not installed during the default installation of Excel so it may need to be added to your configuration of the program.  To determine if Solver is installed, select Tools from Excel's main menu.  If Solver is installed, it will be included in the Tools menu.  If Solver is not present on the Tools menu, it can be installed by selecting Add-ins from the Tools menu or from the Excel installation disk.  For additional installation details, search the Excel help system using the keyword \"Solver\"."]
[null,"Solver - Excel 2007 and later. The Optimizer Worksheet also requires the Solver Add-in that is included with MS Excel.  Solver is not installed during the default installation of Excel so it may need to be added to your configuration of the program.  To determine if Solver is installed, click the \"File\" tab on the top menu bar and select \"Options\" from the left navigation menu.  In the Options windows select The \"Add-Ins\" group. Then check that the \"Solver Add-in\" is included in the list of \"Active Application Add-ins\".  If it is not listed, Use the \"Manage\" dropdown for \"Excel Add-ins\" at the bottom of the window to Browse for the Add-in on your installation media for Excel.  For additional installation details, search the Excel help system using the keyword \"Solver\"."]
[]
[null,"3.  Planning a Compost Recipe or Mix"]
[null,"Steps 1 through 4 in the planning process are described below and are accomplished by entering invormation into green-colored cells within the Optimizer Worksheet."]
[null,"Step 1a.  Use the pull down menu to select each of the materials to be used in your compost mix and indicate whether the cost basis for each material will be set on a weight (lbs) or volume (cubic yard [yd3]) basis.  These settings have a significant impact on the final results of the optimized compost mixture.  Be sure that each of the materials rows you are not using has the \"Empty\" item (bottom of pull down list) selected."]
[null,"Step 1b.  The Optimizer Worksheet will insert default values for material characteristics and cost which have been stored in Materials Library Worksheet.  If desired, modify characterisitcs and cost values for your materials.  The characteristics that are most likely to change are percent moisture and cost.  Note again,  that cost can be set per unit of weight (lbs) or volume (yd3)."]
[null,"Step 2.  For animal tissue and limited amounts of materials, set the amount of each material to be included in the compost mix.  Two types of constraints are supported.  An “Equals” constraint for a material means that only the exact amount specified in the “Equals” constraint cell will be used in the mix during optimization.  A “Range” constraint means that at least the minimum constraint amount but not more than the maximum constraint amount of the material will be used in the mix during optimization.  Constraints on \"Empty\" material rows are ignored during optimization."]
[null,"Step 3.  If the information is known, it is possible to set performance constraints on percent moisture, C:N, and/or bulk density.  If this information is not available for the specific materials being used, the Optimizer will use default constraints which should provide acceptable composting activity.  In each case you can choose among no optimization, an “Equals” constraint, or a “Range” constraint."]
[null,"Step 4.  Enter the size of your loader bucket in cubic yards.  This information will be used to calculate the number of buckets of each material you will need to prepare your compost mix."]
[null,"Step 5.  To automatically optimize a compost mix, simply press the \"Optimize Compost Mix\" button.  The Solver will calculate and display the resulting recipee in the \"Amounts in Mix\" column that satisfy the input constraints while minimizing cost.  Note that not all material combinations will result in a mix that meets all constraints.  But, the Solver will try to meet as many constraints as possible.  To explore alternative mixtures, you can also manually change a compost recipe at any time by entering/modifying amounts into the \"Amounts in Mix\" column.  The information in the “Performance Summary and Materials Summary” tables will indicate whether you are meeting your constraints and suggest recommendations for changes that may allow you to meet all constraints and better optimize the compost recipe."]
[null,"Step 6.  Select the Report Worksheet and print the report to use as a batch sheet when you actually make the compost mix."]
[]
[null,"4.  Tips for Using Spartan Compost Recipe Optimizer"]
[null,"a.  Animal tissue as whole carcass or portions thereof:  If you are composting whole carcasses, start by optimizing a compost recipe without including the animal tissue.  Determine that the mix meets performance \"Range\" constraints without the animal tissue present.  If you are using Spartan Compost Recipe Optimizer to compost portions of the animal carcass, start by selecting all materials, including animal tissue, you are trying to recycle as materials to use in your compost mix.  For each of these materials, indicate whether you are specifying amounts in lbs or yds3.  Next, estimate the amounts of each of these materials you need to dispose of and enter these values into the \"Equals\" constraint cells for each material.  Set the material constraints for each of these materials to \"Equals\" constraint."]
[null,"b.  Optimizing a mix:  Start by setting performance range constraints on percent moisture and C:N.  Press the \"Optimize Compost Mix\" button to optimize the mix.  Then, if you want to fine tune the recipe, add a performance range constraint for bulk density and optimze the mix again.  Finally, manually adjust material amounts to round numbers that can be easily measured.  Switch to the Report Worksheet to print the report."]
[null,"c. Forcing ingrediants into a mix: Due to availability or cost it is often desirable for particular compost ingredients to be maximized or limited in a specific recipe.  To achieve this effect, compost recipe ingredients can be “forced” into the recipe at a specific rate by specifying an “equals” constraint under the compost mix constraints.  But it must be kept in mind that “forcing” ingredients into a mix can also result in unplanned exclusions or maximal use of other ingredients in the final mixture because the Spartan ATC Compost Optimizer operates using a least cost equation.   A process of trial and error adjustments to the inclusion rates, performance constraints or amendment cost may be required to determine an acceptable balance."]
[null,"d.  Percent moisture:  The percent moisture of materials, especially wet materials, can differ significantly from book values in the Materials Worksheet.  Replacing these book values with up to date known values for your materials will provide a more realistic plan for your compost mix and provide more accurate cost estimates.  Quick changes to percent moisture or other composition characteristics can be made right in the green cells in the table in \"Step 1\" at the top in the Optimizer Worksheet."]
[null,"e.  Troubleshooting, No solution:  If automatic optimization is not finding a solution, double check to make sure that none of your constraint values are blank.  At a minimum, a “zero” value must be entered.  For instance, if you specified a range constraint on bulk density but the minimum constraint value cell is blank; the Solver may try to use negative amounts of that material because this reduces overall cost of the mix."]
[null,"If the optimization finds a solution where all material amounts are zero or negligible check material constraints to be sure that at least one material minimum constraint or equals constraint is greater than one."]
[null,"f.Troubleshooting, Least Cost Optimzation:The Spartan ATC Compost Optimizer’s least cost optimization process is dependent on the individual prices associated with each of the included compost amendment materials.  If total cost of the compost recipe is an important consideration, accurate individual amendment costs should be determined and entered when choosing amendments in step 1.  But, even if the cost of the compost recipe is not a primary concern, the least cost optimization function still impacts the resulting compost recipe.  For recipe mixtures where total cost is not a primary concern, equalizing all ingredient costs when choosing amendments in step 1 will allow the optimizer to function as expected."]
[null,"g.  \"Compile error: Can't find project or library\".  If you get this error when you press the \"Optimize Compost Mix\" button, you do not have the Solver installed.  See the Solver section in systems requirements above for instructions on installing the Solver Add-in.  If the Spartan Compost Recipe Optimizer Spreadsheet is open when you install Solver, you will need to close and reopen it before pressing the \"Optimize Compost Mix\" button."]
[null,"h.  If after adding or deleting materials from the library of materials in the Materials Worksheet, you  have different or unexpected materials appearing in the table at the top of the Optimizer Worksheet,  then the following action is needed.  After you change the materials library, you need to reselect materials in “Step 1” of the Optimizer Worksheet to resynchronize the information in the Optimizers with that in the Materials Worksheet."]
[]
[null,"Changes since version 1.0"]
[null,"v1.0 Oct. 8, 2007:  Version 1.0 rolled out at MSUE Fall Extension Conference."]
[null,"v1.01 Oct. 22, 2007: Corrected errors in instructions sheet."]
[null,"v1.02 Oct. 21, 2008:  Modified protection on Materials sheet so users can insert, delete rows and sort list.  "]
[null,"Do not include the 'empty' row if you resort materials."]
[null,"v1.03 Oct. 8, 2013. Revised application to work with Excel 2007 or newer.  Updated materials library to include more materials."]
[null,"v.1.04 Feb. 28, 2014.  Correct bug in optimization tableau; on Report tab sheet added mix summary weighted averages for columns carbon and nitrogen; and on the Optimizer sheet Step 5 reformatted the results for percent moisture, C:N and bulk density to one decimal place so the Performance Summary text recommendations precisely match the numeric values at boundries where recommendations change. "]
[null,"v 1.05 Jul. 6, 2017.  Corrected bug in optimization strategy for C:N and percent moisture."]

{"sheet":"Optimizer","index":1,"n_rows":86,"n_cols":26,"rows_kept":86,"cut":false,"hidden":false,"merged":[[81,2,81,6],[74,7,74,9],[51,23,51,25],[52,23,52,25],[78,7,78,9],[70,7,70,9],[71,7,71,9],[72,7,72,9],[76,7,76,9],[75,7,75,9],[51,14,51,15],[77,7,77,9],[67,7,67,9],[73,7,73,9],[67,3,67,5],[76,3,76,5],[7,4,7,6],[64,7,64,9],[65,7,65,9],[66,7,66,9],[35,3,35,5],[35,6,35,7],[36,6,36,7],[21,3,21,5],[21,7,21,8],[31,2,31,8],[40,2,40,7],[32,2,32,8],[21,14,21,16],[64,3,64,5],[65,3,65,5],[66,3,66,5],[65,14,65,17],[22,7,22,8],[57,23,57,25],[78,3,78,5],[70,3,70,5],[71,3,71,5],[72,3,72,5],[73,3,73,5],[74,3,74,5],[75,3,75,5],[77,3,77,5]]}
[]
[null,null,"Optimzer Worksheet for Spartan Compost Recipe Optimizer",null,null,null,null,null,null,null,null,null,null,"hide columns -------->",null,null,null,null,null,null,null,null,null,null,null," --- end hide columns  --->"]
[]
[null,"Step 1.","Select materials to use in compost mix and indicate whether material amounts and constraints will be in lbs or yds3."]
[null,null,"Adjust material composition and cost as needed."]
[null,null,"NOTE:  table cells with a green background are user inputs, white cells are calculated values."]
[]
[null,null,"Select Materials to Use","Mix","Cost",null,null,"Moisture","Carbon","Nitrogen","Bulk Density"]
[null,null,"in Compost Mix","Units","$","Amount","Units","%","% of wet wt","% of wet wt","lbs/yd3"]
[null,1,null,null,0,2000,null,0,0,0,0]
[null,2,null,null,0,2000,null,0,0,0,0]
[null,3,null,null,0,2000,null,0,0,0,0]
[null,4,null,null,0,2000,null,0,0,0,0]
[null,5,null,null,0,2000,null,0,0,0,0]
[null,6,null,null,0,2000,null,0,0,0,0]
[null,7,null,null,0,2000,null,0,0,0,0]
[null,8,null,null,0,2000,null,0,0,0,0]
[]
[null,"Step 2.","Set material constraints for each material in the mix."]
[null,null,"Constraint settings will be ignored for \"Empty\" material rows."]
[]
[null,null,"Materials in","Constraint Values",null,null,null,"Select Constraints",null,null,null,null,null,null,"Constraint Values in lbs"]
[null,null,"Compost Mix","Min","Equals","Max","Units","to Use",null,null,null,null,null,null,{"$f":"D23","v":"Min"},{"$f":"E23","v":"Equals"},{"$f":"F23","v":"Max"}]
[null,1,{"$f":"HiddenSheet!B18","v":"Empty"},0,1000,0,{"$f":"HiddenSheet!E18","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D18=IsLbs,1,$K10) * D24","v":0},{"$f":"IF(HiddenSheet!$D18=IsLbs,1,$K10) * E24","v":1000},{"$f":"IF(HiddenSheet!$D18=IsLbs,1,$K10) * F24","v":0}]
[null,2,{"$f":"HiddenSheet!B19","v":"Empty"},0,0,20000,{"$f":"HiddenSheet!E19","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D19=IsLbs,1,$K11) * D25","v":0},{"$f":"IF(HiddenSheet!$D19=IsLbs,1,$K11) * E25","v":0},{"$f":"IF(HiddenSheet!$D19=IsLbs,1,$K11) * F25","v":20000}]
[null,3,{"$f":"HiddenSheet!B20","v":"Empty"},0,0,20000,{"$f":"HiddenSheet!E20","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D20=IsLbs,1,$K12) * D26","v":0},{"$f":"IF(HiddenSheet!$D20=IsLbs,1,$K12) * E26","v":0},{"$f":"IF(HiddenSheet!$D20=IsLbs,1,$K12) * F26","v":20000}]
[null,4,{"$f":"HiddenSheet!B21","v":"Empty"},0,0,20000,{"$f":"HiddenSheet!E21","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D21=IsLbs,1,$K13) * D27","v":0},{"$f":"IF(HiddenSheet!$D21=IsLbs,1,$K13) * E27","v":0},{"$f":"IF(HiddenSheet!$D21=IsLbs,1,$K13) * F27","v":20000}]
[null,5,{"$f":"HiddenSheet!B22","v":"Empty"},0,0,20000,{"$f":"HiddenSheet!E22","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D22=IsLbs,1,$K14) * D28","v":0},{"$f":"IF(HiddenSheet!$D22=IsLbs,1,$K14) * E28","v":0},{"$f":"IF(HiddenSheet!$D22=IsLbs,1,$K14) * F28","v":20000}]
[null,6,{"$f":"HiddenSheet!B23","v":"Empty"},0,0,20000,{"$f":"HiddenSheet!E23","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D23=IsLbs,1,$K15) * D29","v":0},{"$f":"IF(HiddenSheet!$D23=IsLbs,1,$K15) * E29","v":0},{"$f":"IF(HiddenSheet!$D23=IsLbs,1,$K15) * F29","v":20000}]
[null,7,{"$f":"HiddenSheet!B24","v":"Empty"},0,0,20000,{"$f":"HiddenSheet!E24","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D24=IsLbs,1,$K16) * D30","v":0},{"$f":"IF(HiddenSheet!$D24=IsLbs,1,$K16) * E30","v":0},{"$f":"IF(HiddenSheet!$D24=IsLbs,1,$K16) * F30","v":20000}]
[null,8,{"$f":"HiddenSheet!B25","v":"Empty"},0,0,20000,{"$f":"HiddenSheet!E25","v":"lbs"},null,null,null,null,null,null,null,{"$f":"IF(HiddenSheet!$D25=IsLbs,1,$K17) * D31","v":0},{"$f":"IF(HiddenSheet!$D25=IsLbs,1,$K17) * E31","v":0},{"$f":"IF(HiddenSheet!$D25=IsLbs,1,$K17) * F31","v":20000}]
[null,null,"If you select an “Equals constraint” for a material, enter the constraint value on the same row in the Constraint Values ‘Equals’ column.  Alternately, if you select a ‘Range constraint’ for a material enter both ‘Min’ and ‘Max’ Constraint values for the material."]
[null,null,"If you think a material should be included in the mix set a minimum constraint on that material that is greater than 10% of material with the equals constraint."]
[null,"Step 3.","Set performance constraints."]
[]
[null,null,"Performance","Constraint Values",null,null,"Select Constraints"]
[null,null,"Constraint","Min","Equals","Max","to Use"]
[null,null,"Percent Moisture",40,null,60]
[null,null,"Carbon : Nitrogen",30,null,60,0]
[null,null,"Bulk Density (lbs/yd3)",600,null,1200]
[null,null,"If you select an “Equals constraint” for a performance criterion, enter the constraint value on the same row in the Constraint Values ‘Equals’ column.  Alternately, if you select a ‘Range constraint’ for a performance criterion  enter both ‘Min’ and ‘Max’ Constraint values.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"`"]
[null,"Step 4.","Set size of loader bucket in cubic yards.  This information is used to calculate the number of yd3"]
[null,null,"of each material that are needed to prepare your compost mix."]
[]
[null,null,"Loader bucket size:",1.5,"yd3"]
[]
[null,"Step 5.","Determine materials amounts in compost mix to meet performance and material constraints.  "]
[null,null,"First, press the \"Optimize Compost Mix\" button to automatically set material amounts.",null,null,null,null,null,null,null,null,null,null,null,"Intermediate Calculations used in Summarizing a Mix."]
[null,null,"Depending on the materials and constraints you selected, Excel's Solver may or may not meet all constraints."]
[null,null,"Fine tune the mix by manually modifying values in the \"Amount in Mix\" column."]
[]
[null,null,"Materials in","Amount","Amount","Amount","Moisture","C : N","Bulk Density","Water : C","Total",null,null,null,"Calculated Amount",null,"Water","Carbon","Nitrogen","Cost","Tissue","Amendments",null,"Simplex performance constraint equations"]
[null,null,"Compost Mix",0,"units","Buckets","%",null,"lbs/yd3",null,"Cost",null,null,null,"lbs","yd3","lbs","lbs","lbs","$/ton","lbs","yd3",null,"for ratios that meet linearity assumptions"]
[null,1,{"$f":"HiddenSheet!B18","v":"Empty"},0,{"$f":"HiddenSheet!E18","v":"lbs"},{"$f":"IF(BucketSize>0,P54/BucketSize,\"\")","v":0},{"$f":"H10","v":0},{"$f":"IF(J10>0,I10/J10,0)","v":0},{"$f":"K10","v":0},{"$f":"IF(I10>0, H10/I10, 0)","v":0},{"$f":"IF(Material1Index>=NumMaterials, 0, O54/2000 * Optimizer!T54)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D18=IsLbs,D54,D54*I54)","v":0},{"$f":"IF(I54>0, O54/I54, 0)","v":0},{"$f":"O54 * H10/100","v":0},{"$f":"O54 * I10/100","v":0},{"$f":"O54 * J10/100","v":0},{"$f":"E10 / IF(HiddenSheet!F18=IsLbs,Optimizer!F10,F10*I54) * 2000","v":0},{"$f":"IF(HiddenSheet!H18=1,Optimizer!O54,0)","v":0},{"$f":"IF(HiddenSheet!H18<>1,Optimizer!P54,0)","v":0},null,{"$f":"R62-CNMinConstraint*S62","v":0}," >= 0","C:N Min range constraint"]
[null,2,{"$f":"HiddenSheet!B19","v":"Empty"},0,{"$f":"HiddenSheet!E19","v":"lbs"},0,0,0,0,0,{"$f":"IF(Material1Index>=NumMaterials, 0, O55/2000 * Optimizer!T55)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D19=IsLbs,D55,D55*I55)","v":0},0,0,0,0,{"$f":"E11 / IF(HiddenSheet!F19=IsLbs,Optimizer!F11,F11*I55) * 2000","v":0},{"$f":"IF(HiddenSheet!H19=1,Optimizer!O55,0)","v":0},{"$f":"IF(HiddenSheet!H19<>1,Optimizer!P55,0)","v":0},null,{"$f":"R62-CNMaxConstraint*S62","v":0}," <= 0","C:N Max range constraint"]
[null,3,{"$f":"HiddenSheet!B20","v":"Empty"},0,{"$f":"HiddenSheet!E20","v":"lbs"},0,0,0,0,0,{"$f":"IF(Material1Index>=NumMaterials, 0, O56/2000 * Optimizer!T56)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D20=IsLbs,D56,D56*I56)","v":0},0,0,0,0,{"$f":"E12 / IF(HiddenSheet!F20=IsLbs,Optimizer!F12,F12*I56) * 2000","v":0},{"$f":"IF(HiddenSheet!H20=1,Optimizer!O56,0)","v":0},{"$f":"IF(HiddenSheet!H20<>1,Optimizer!P56,0)","v":0},null,{"$f":"R62 - CNEqualsConstraint*S62","v":0},"   = 0","C:N equals constraint"]
[null,4,{"$f":"HiddenSheet!B21","v":"Empty"},0,{"$f":"HiddenSheet!E21","v":"lbs"},0,0,0,0,0,{"$f":"IF(Material1Index>=NumMaterials, 0, O57/2000 * Optimizer!T57)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D21=IsLbs,D57,D57*I57)","v":0},0,0,0,0,{"$f":"E13 / IF(HiddenSheet!F21=IsLbs,Optimizer!F13,F13*I57) * 2000","v":0},{"$f":"IF(HiddenSheet!H21=1,Optimizer!O57,0)","v":0},{"$f":"IF(HiddenSheet!H21<>1,Optimizer!P57,0)","v":0}]
[null,5,{"$f":"HiddenSheet!B22","v":"Empty"},0,{"$f":"HiddenSheet!E22","v":"lbs"},0,0,0,0,0,{"$f":"IF(Material1Index>=NumMaterials, 0, O58/2000 * Optimizer!T58)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D22=IsLbs,D58,D58*I58)","v":0},0,0,0,0,{"$f":"E14 / IF(HiddenSheet!F22=IsLbs,Optimizer!F14,F14*I58) * 2000","v":0},{"$f":"IF(HiddenSheet!H22=1,Optimizer!O58,0)","v":0},{"$f":"IF(HiddenSheet!H22<>1,Optimizer!P58,0)","v":0},null,"Compost Mix Summary"]
[null,6,{"$f":"HiddenSheet!B23","v":"Empty"},0,{"$f":"HiddenSheet!E23","v":"lbs"},0,0,0,0,0,{"$f":"IF(Material1Index>=NumMaterials, 0, O59/2000 * Optimizer!T59)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D23=IsLbs,D59,D59*I59)","v":0},0,0,0,0,{"$f":"E15 / IF(HiddenSheet!F23=IsLbs,Optimizer!F15,F15*I59) * 2000","v":0},{"$f":"IF(HiddenSheet!H23=1,Optimizer!O59,0)","v":0},{"$f":"IF(HiddenSheet!H23<>1,Optimizer!P59,0)","v":0},null,null,"Percent Moisture",{"$f":"IF(O62>0, Q62/O62 * 100, 0)","v":0}]
[null,7,{"$f":"HiddenSheet!B24","v":"Empty"},0,{"$f":"HiddenSheet!E24","v":"lbs"},0,0,0,0,0,{"$f":"IF(Material1Index>=NumMaterials, 0, O60/2000 * Optimizer!T60)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D24=IsLbs,D60,D60*I60)","v":0},0,0,0,0,{"$f":"E16 / IF(HiddenSheet!F24=IsLbs,Optimizer!F16,F16*I60) * 2000","v":0},{"$f":"IF(HiddenSheet!H24=1,Optimizer!O60,0)","v":0},{"$f":"IF(HiddenSheet!H24<>1,Optimizer!P60,0)","v":0},null,null,"C:N",{"$f":"IF(S62>0, R62/S62, 0)","v":0}]
[null,8,{"$f":"HiddenSheet!B25","v":"Empty"},0,{"$f":"HiddenSheet!E25","v":"lbs"},0,0,0,0,0,{"$f":"IF(Material1Index>=NumMaterials, 0, O61/2000 * Optimizer!T61)","v":0},null,null,null,{"$f":"IF(HiddenSheet!D25=IsLbs,D61,D61*I61)","v":0},0,0,0,0,{"$f":"E17 / IF(HiddenSheet!F25=IsLbs,Optimizer!F17,F17*I61) * 2000","v":0},{"$f":"IF(HiddenSheet!H25=1,Optimizer!O61,0)","v":0},{"$f":"IF(HiddenSheet!H25<>1,Optimizer!P61,0)","v":0},null,null,"Bulk Density",{"$f":"IF(P62>0, O62/P62, 0)","v":0}]
[null,null," Compost Mix Summary  ",null,null,{"$f":"SUM(F54:F61)","v":0},{"$f":"ROUND(calc_PctMoisture,1)","v":0},{"$f":"ROUND(calc_CNRatio,1)","v":0},{"$f":"ROUND(calc_BulkDensity,1)","v":0},{"$f":"IF(R62>0, Q62/R62, 0)","v":0},{"$f":"SUM(K54:K61)","v":0},null,null,null,{"$f":"SUM(O54:O61)","v":0},0,0,0,0,0,0,0]
[]
[null,null,"Performance Summary"]
[null,null,"Category","Constraints",null,null,"Mix","Recommendations"]
[null,null,"Percent Moisture",{"$f":"IF(HiddenSheet!D29=PerformanceNoOptimization, \"No criteria set\", IF(HiddenSheet!D29=PerformanceEqualsConstraint,\"Percent Moisture = \"&E38, D38&\" <= Percent Moisture <= \"&F38) )","v":"40 <= Percent Moisture <= 60"},null,null,{"$f":"G62","v":0},{"$f":"IF(HiddenSheet!D29=PerformanceNoOptimization, \"\", IF(HiddenSheet!D29=PerformanceEqualsConstraint,IF(G62<E38,\"Increase percent moisture\",IF(G62>E38,\"Decrease percent moisture\",\"Percent Moisture OK\")), IF(G62<D38,\"Increase percent moisture\",IF(G62>F38, \"Decrease percent moisture\",\"Percent Moisture OK\"))) )","v":"Increase percent moisture"},null,null,null,null,null,null,"Solver Model Saved Here"]
[null,null,"Carbon : Nitrogen",{"$f":"IF(HiddenSheet!D30=PerformanceNoOptimization, \"No criteria set\", IF(HiddenSheet!D30=PerformanceEqualsConstraint,\"C:N = \"&E39, D39&\" <= C:N <= \"&F39) )","v":"30 <= C:N <= 60"},null,null,{"$f":"H62","v":0},{"$f":"IF(HiddenSheet!D30=PerformanceNoOptimization, \"\", IF(HiddenSheet!D30=PerformanceEqualsConstraint,IF(H62<E39,\"Add carbon or remove nitrogen\",IF(H62>E39,\"Remove carbon or add nitrogen\",\"C:N ratio OK\")), IF(H62<D39,\"Add nitrogen or remove carbon\",IF(H62>F39, \"Remove carbon or add nitrogen\",\"C:N ratio OK\"))) )","v":"Add nitrogen or remove carbon"},null,null,null,null,null,null,null,"Objective function",{"$f":"MIN($K$62)","v":0}]
[null,null,"Bulk Density (lbs/yd3)",{"$f":"IF(HiddenSheet!D31=PerformanceNoOptimization, \"No criteria set\", IF(HiddenSheet!D31=PerformanceEqualsConstraint,\"Bulk Density = \"&E40, D40&\" <= Bulk Density <= \"&F40) )","v":"No criteria set"},null,null,{"$f":"I62","v":0},{"$f":"IF(HiddenSheet!D31=PerformanceNoOptimization, \"\", IF(HiddenSheet!D31=PerformanceEqualsConstraint,IF(I62<E40,\"Increase bulk density\",IF(I62>E40,\"Decrease bulk density\",\"Bulk density OK\")), IF(I62<D40,\"Increase bulk density\",IF(I62>F40, \"Decrease bulk density\",\"Bulk density OK\"))) )","v":""},null,null,null,null,null,null,null,"Cells to change",{"$f":"COUNT($D$54:$D$61)","v":8}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Amount2>=0","v":true}]
[null,null,"Materials Summary",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Amount3<=20000","v":true}]
[null,null,"Material","Constraints",null,null,"Units","Recommendations",null,null,null,null,null,null,null,null,{"$f":"Amount2<=20000","v":true}]
[null,1,{"$f":"HiddenSheet!B18","v":"Empty"},{"$f":"IF(HiddenSheet!C18>=Materials!$B$56,\"\", IF(HiddenSheet!G18=HiddenSheet!$C$8,HiddenSheet!B18&\" = \"&E24,D24&\"<=\"&HiddenSheet!B18&\"<=\"&F24))","v":""},null,null,{"$f":"HiddenSheet!E18","v":"lbs"},{"$f":"IF(HiddenSheet!C18>=NumMaterials, \"\", IF(HiddenSheet!G18=MaterialsEqualsConstraint,IF(O54<P24, \"Add more \"&HiddenSheet!B18,IF(O54>P24,\"Remove \"&HiddenSheet!B18,\"Amount OK\")), IF(O54<O24,\"Add more \"&HiddenSheet!B18,IF(O54>Q24, \"Remove \"&HiddenSheet!B18,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount3>=0","v":true}]
[null,2,{"$f":"HiddenSheet!B19","v":"Empty"},{"$f":"IF(HiddenSheet!C19>=Materials!$B$56,\"\", IF(HiddenSheet!G19=HiddenSheet!$C$8,HiddenSheet!B19&\" = \"&E25,D25&\" <= \"&HiddenSheet!B19&\" <= \"&F25))","v":""},null,null,{"$f":"HiddenSheet!E19","v":"lbs"},{"$f":"IF(HiddenSheet!C19>=NumMaterials, \"\", IF(HiddenSheet!G19=MaterialsEqualsConstraint,IF(O55<P25,\"Add more \"&HiddenSheet!B19,IF(O55>P25,\"Remove \"&HiddenSheet!B19,\"Amount OK\")), IF(O55<O25,\"Add more \"&HiddenSheet!B19,IF(O55>Q25, \"Remove \"&HiddenSheet!B19,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount4>=0","v":true}]
[null,3,{"$f":"HiddenSheet!B20","v":"Empty"},{"$f":"IF(HiddenSheet!C20>=Materials!$B$56,\"\", IF(HiddenSheet!G20=HiddenSheet!$C$8,HiddenSheet!B20&\" = \"&E26,D26&\" <= \"&HiddenSheet!B20&\" <= \"&F26))","v":""},null,null,{"$f":"HiddenSheet!E20","v":"lbs"},{"$f":"IF(HiddenSheet!C20>=NumMaterials, \"\", IF(HiddenSheet!G20=MaterialsEqualsConstraint,IF(O56<P26,\"Add more \"&HiddenSheet!B20,IF(O56>P26,\"Remove \"&HiddenSheet!B20,\"Amount OK\")), IF(O56<O26,\"Add more \"&HiddenSheet!B20,IF(O56>Q26, \"Remove \"&HiddenSheet!B20,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount5<=20000","v":true}]
[null,4,{"$f":"HiddenSheet!B21","v":"Empty"},{"$f":"IF(HiddenSheet!C21>=Materials!$B$56,\"\", IF(HiddenSheet!G21=HiddenSheet!$C$8,HiddenSheet!B21&\" = \"&E27,D27&\" <= \"&HiddenSheet!B21&\" <= \"&F27))","v":""},null,null,{"$f":"HiddenSheet!E21","v":"lbs"},{"$f":"IF(HiddenSheet!C21>=NumMaterials, \"\", IF(HiddenSheet!G21=MaterialsEqualsConstraint,IF(O57<P27,\"Add more \"&HiddenSheet!B21,IF(O57>P27,\"Remove \"&HiddenSheet!B21,\"Amount OK\")), IF(O57<O27,\"Add more \"&HiddenSheet!B21,IF(O57>Q27, \"Remove \"&HiddenSheet!B21,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount4<=20000","v":true}]
[null,5,{"$f":"HiddenSheet!B22","v":"Empty"},{"$f":"IF(HiddenSheet!C22>=Materials!$B$56,\"\", IF(HiddenSheet!G22=HiddenSheet!$C$8,HiddenSheet!B22&\" = \"&E28,D28&\" <= \"&HiddenSheet!B22&\" <= \"&F28))","v":""},null,null,{"$f":"HiddenSheet!E22","v":"lbs"},{"$f":"IF(HiddenSheet!C22>=NumMaterials, \"\", IF(HiddenSheet!G22=MaterialsEqualsConstraint,IF(O58<P28,\"Add more \"&HiddenSheet!B22,IF(O58>P28,\"Remove \"&HiddenSheet!B22,\"Amount OK\")), IF(O58<O28,\"Add more \"&HiddenSheet!B22,IF(O58>Q28, \"Remove \"&HiddenSheet!B22,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount5>=0","v":true}]
[null,6,{"$f":"HiddenSheet!B23","v":"Empty"},{"$f":"IF(HiddenSheet!C23>=Materials!$B$56,\"\", IF(HiddenSheet!G23=HiddenSheet!$C$8,HiddenSheet!B23&\" = \"&E29,D29&\" <= \"&HiddenSheet!B23&\" <= \"&F29))","v":""},null,null,{"$f":"HiddenSheet!E23","v":"lbs"},{"$f":"IF(HiddenSheet!C23>=NumMaterials, \"\", IF(HiddenSheet!G23=MaterialsEqualsConstraint,IF(O59<P29,\"Add more \"&HiddenSheet!B23,IF(O59>P29,\"Remove \"&HiddenSheet!B23,\"Amount OK\")), IF(O59<O29,\"Add more \"&HiddenSheet!B23,IF(O59>Q29, \"Remove \"&HiddenSheet!B23,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount6=0","v":true}]
[null,7,{"$f":"HiddenSheet!B24","v":"Empty"},{"$f":"IF(HiddenSheet!C24>=Materials!$B$56,\"\", IF(HiddenSheet!G24=HiddenSheet!$C$8,HiddenSheet!B24&\" = \"&E30,D30&\" <= \"&HiddenSheet!B24&\" <= \"&F30))","v":""},null,null,{"$f":"HiddenSheet!E24","v":"lbs"},{"$f":"IF(HiddenSheet!C24>=NumMaterials, \"\", IF(HiddenSheet!G24=MaterialsEqualsConstraint,IF(O60<P30,\"Add more \"&HiddenSheet!B24,IF(O60>P30,\"Remove \"&HiddenSheet!B24,\"Amount OK\")), IF(O60<O30,\"Add more \"&HiddenSheet!B24,IF(O60>Q30, \"Remove \"&HiddenSheet!B24,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount7=0","v":true}]
[null,8,{"$f":"HiddenSheet!B25","v":"Empty"},{"$f":"IF(HiddenSheet!C25>=Materials!$B$56,\"\", IF(HiddenSheet!G25=HiddenSheet!$C$8,HiddenSheet!B25&\" = \"&E31,D31&\" <= \"&HiddenSheet!B25&\" <= \"&F31))","v":""},null,null,{"$f":"HiddenSheet!E25","v":"lbs"},{"$f":"IF(HiddenSheet!C25>=NumMaterials, \"\", IF(HiddenSheet!G25=MaterialsEqualsConstraint,IF(O61<P31,\"Add more \"&HiddenSheet!B25,IF(O61>P31,\"Remove \"&HiddenSheet!B25,\"Amount OK\")), IF(O61<O31,\"Add more \"&HiddenSheet!B25,IF(O61>Q31, \"Remove \"&HiddenSheet!B25,\"Amount OK\"))) )","v":""},null,null,null,null,null,null,null,null,{"$f":"Amount8=0","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Amount1=1000","v":false}]
[null,null,"Animal Tissue Density",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"LinearCNMaxRangeConstraint<=0","v":true}]
[null,null,"pounds of animal tissue / ft3 of all other non-animal materials:  ",null,null,null,null,{"$f":"IF(Amendments>0,((MortailtyAgent)/Amendments) /27, 0)","v":0},null,null,null,null,null,null,null,null,{"$f":"LinearCNMinRangeConstraint>=0","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"PercentMoisture>=40","v":false}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"PercentMoisture<=60","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"{100,100,0.0001,0.05,FALSE,FALSE,FALSE,1,2,1,0.0001,FALSE}","v":100}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"{100,100,1,100,0,FALSE,FALSE,0.075,0,0,FALSE,30}","v":100}]

{"sheet":"Report","index":2,"n_rows":34,"n_cols":10,"rows_kept":34,"cut":false,"hidden":false,"merged":[[29,6,29,8],[30,6,30,8],[31,6,31,8],[32,6,32,8],[25,6,25,8],[26,6,26,8],[27,6,27,8],[28,6,28,8],[29,2,29,4],[30,2,30,4],[31,2,31,4],[32,2,32,4],[25,2,25,4],[26,2,26,4],[27,2,27,4],[28,2,28,4],[20,6,20,8],[21,6,21,8],[24,2,24,4],[24,6,24,8],[20,2,20,4],[21,2,21,4],[0,7,0,8],[18,2,18,4],[19,2,19,4],[18,6,18,8],[19,6,19,8]]}
[null,null,null,null,"Spartan Compost Recipe Optimizer",null,null,{"$f":"NOW()","v":"2018-07-09T14:18:46.590"}]
[]
[]
[null,"Materials in","Amount","Amount","Amount","Moisture","Carbon","Nitrogen","Bulk Density","Total"]
[null,"Compost Mix","lbs","yd3","buckets","%","% of wet wt","% of wet wt","lbs/yd3","Cost"]
[null,{"$f":"Optimizer!C54","v":"Empty"},{"$f":"Optimizer!O54","v":0},{"$f":"Optimizer!P54","v":0},{"$f":"Optimizer!F54","v":0},{"$f":"Optimizer!H10","v":0},{"$f":"Optimizer!I10","v":0},{"$f":"Optimizer!J10","v":0},{"$f":"Optimizer!I54","v":0},{"$f":"Optimizer!K54","v":0}]
[null,{"$f":"Optimizer!C55","v":"Empty"},{"$f":"Optimizer!O55","v":0},{"$f":"Optimizer!P55","v":0},{"$f":"Optimizer!F55","v":0},{"$f":"Optimizer!H11","v":0},{"$f":"Optimizer!I11","v":0},{"$f":"Optimizer!J11","v":0},{"$f":"Optimizer!I55","v":0},{"$f":"Optimizer!K55","v":0}]
[null,{"$f":"Optimizer!C56","v":"Empty"},{"$f":"Optimizer!O56","v":0},{"$f":"Optimizer!P56","v":0},{"$f":"Optimizer!F56","v":0},{"$f":"Optimizer!H12","v":0},{"$f":"Optimizer!I12","v":0},{"$f":"Optimizer!J12","v":0},{"$f":"Optimizer!I56","v":0},{"$f":"Optimizer!K56","v":0}]
[null,{"$f":"Optimizer!C57","v":"Empty"},{"$f":"Optimizer!O57","v":0},{"$f":"Optimizer!P57","v":0},{"$f":"Optimizer!F57","v":0},{"$f":"Optimizer!H13","v":0},{"$f":"Optimizer!I13","v":0},{"$f":"Optimizer!J13","v":0},{"$f":"Optimizer!I57","v":0},{"$f":"Optimizer!K57","v":0}]
[null,{"$f":"Optimizer!C58","v":"Empty"},{"$f":"Optimizer!O58","v":0},{"$f":"Optimizer!P58","v":0},{"$f":"Optimizer!F58","v":0},{"$f":"Optimizer!H14","v":0},{"$f":"Optimizer!I14","v":0},{"$f":"Optimizer!J14","v":0},{"$f":"Optimizer!I58","v":0},{"$f":"Optimizer!K58","v":0}]
[null,{"$f":"Optimizer!C59","v":"Empty"},{"$f":"Optimizer!O59","v":0},{"$f":"Optimizer!P59","v":0},{"$f":"Optimizer!F59","v":0},{"$f":"Optimizer!H15","v":0},{"$f":"Optimizer!I15","v":0},{"$f":"Optimizer!J15","v":0},{"$f":"Optimizer!I59","v":0},{"$f":"Optimizer!K59","v":0}]
[null,{"$f":"Optimizer!C60","v":"Empty"},{"$f":"Optimizer!O60","v":0},{"$f":"Optimizer!P60","v":0},{"$f":"Optimizer!F60","v":0},{"$f":"Optimizer!H16","v":0},{"$f":"Optimizer!I16","v":0},{"$f":"Optimizer!J16","v":0},{"$f":"Optimizer!I60","v":0},{"$f":"Optimizer!K60","v":0}]
[null,{"$f":"Optimizer!C61","v":"Empty"},{"$f":"Optimizer!O61","v":0},{"$f":"Optimizer!P61","v":0},{"$f":"Optimizer!F61","v":0},{"$f":"Optimizer!H17","v":0},{"$f":"Optimizer!I17","v":0},{"$f":"Optimizer!J17","v":0},{"$f":"Optimizer!I61","v":0},{"$f":"Optimizer!K61","v":0}]
[null,"Compost Mix Summary",{"$f":"SUM(C6:C13)","v":0},{"$f":"SUM(D6:D13)","v":0},{"$f":"SUM(E6:E13)","v":0},{"$f":"PercentMoisture","v":0},{"$f":"IF(C14>0,SUMPRODUCT(C6:C13,G6:G13)/C14,0)","v":0},{"$f":"IF(C14>0,SUMPRODUCT(C6:C13,H6:H13)/C14,0)","v":0},{"$f":"BulkDensity","v":0},{"$f":"Optimizer!K62","v":0}]
[null,null,null,null,null,null,null,null,"Total Cost/yd3",{"$f":"IF(D14>0,J14/D14,0)","v":0}]
[null,null,null,null,null,null,null,null,"Total cost/ton",{"$f":"IF(C14>0,J14/C14*2000,0)","v":0}]
[]
[null,"Performance Summary"]
[null,"Category","Constraints",null,null,"Mix","Recommendations"]
[null,{"$f":"Optimizer!C66","v":"Percent Moisture"},{"$f":"Optimizer!D66","v":"40 <= Percent Moisture <= 60"},null,null,{"$f":"Optimizer!G66","v":0},{"$f":"Optimizer!H66","v":"Increase percent moisture"}]
[null,{"$f":"Optimizer!C67","v":"Carbon : Nitrogen"},{"$f":"Optimizer!D67","v":"30 <= C:N <= 60"},null,null,{"$f":"Optimizer!G67","v":0},{"$f":"Optimizer!H67","v":"Add nitrogen or remove carbon"}]
[null,"Bulk Density (lbs/yd3)",{"$f":"Optimizer!D68","v":"No criteria set"},null,null,{"$f":"Optimizer!G68","v":0},{"$f":"Optimizer!H68","v":""}]
[]
[null,"Materials Summary"]
[null,"Material","Constraints",null,null,"Units","Recommendations"]
[null,{"$f":"Optimizer!C72","v":"Empty"},{"$f":"Optimizer!D72","v":""},null,null,{"$f":"Optimizer!G72","v":"lbs"},{"$f":"Optimizer!H72","v":""}]
[null,{"$f":"Optimizer!C73","v":"Empty"},{"$f":"Optimizer!D73","v":""},null,null,{"$f":"Optimizer!G73","v":"lbs"},{"$f":"Optimizer!H73","v":""}]
[null,{"$f":"Optimizer!C74","v":"Empty"},{"$f":"Optimizer!D74","v":""},null,null,{"$f":"Optimizer!G74","v":"lbs"},{"$f":"Optimizer!H74","v":""}]
[null,{"$f":"Optimizer!C75","v":"Empty"},{"$f":"Optimizer!D75","v":""},null,null,{"$f":"Optimizer!G75","v":"lbs"},{"$f":"Optimizer!H75","v":""}]
[null,{"$f":"Optimizer!C76","v":"Empty"},{"$f":"Optimizer!D76","v":""},null,null,{"$f":"Optimizer!G76","v":"lbs"},{"$f":"Optimizer!H76","v":""}]
[null,{"$f":"Optimizer!C77","v":"Empty"},{"$f":"Optimizer!D77","v":""},null,null,{"$f":"Optimizer!G77","v":"lbs"},{"$f":"Optimizer!H77","v":""}]
[null,{"$f":"Optimizer!C78","v":"Empty"},{"$f":"Optimizer!D78","v":""},null,null,{"$f":"Optimizer!G78","v":"lbs"},{"$f":"Optimizer!H78","v":""}]
[null,{"$f":"Optimizer!C79","v":"Empty"},{"$f":"Optimizer!D79","v":""},null,null,{"$f":"Optimizer!G79","v":"lbs"},{"$f":"Optimizer!H79","v":""}]
[null,"Rule of thumb:",{"$f":"Optimizer!H82","v":0},"pounds of animal tissue / ft3 of all other non-animal materials"]

{"sheet":"Materials","index":3,"n_rows":65,"n_cols":9,"rows_kept":65,"cut":false,"hidden":false,"merged":[[62,0,62,9],[63,0,63,9],[64,0,64,9],[3,5,3,7],[59,0,59,9],[60,0,60,9],[61,0,61,9]]}
["Materials Library for Spartan Compost Recipe Optimizer"]
["Unless otherwise noted in cell comments, all material characteristics are from the On-Farm Composting Handbook, Appendix A."]
["Date Last Updated:"]
[null,"Moisture","Carbon","Nitrogen","Bulk Density","Cost",null,null,"Material"]
["Material","%","% of wet wt","% of wet wt","lbs/yd3","$","Amount","Units","Type"]
["Apple pomace",88,6.3360000000000003,0.13200000000000001,1559,100,2000,1,2]
["Bark/wood chips",85,10.8,0.03,532,100,2000,1,2]
["Blood wastes",78,10.78,3.08,1700,100,2000,1,2]
["Bovine carcass",70,14.98,2.76,1761.404908,50,1200,1,1]
["Broiler litter",35,25.48,1.82,890,100,2000,1,2]
["Butcher plant by-product",20.8,63.42,0.8,1535,100,2000,1,1]
["Corn cobs",15,49.98,0.51,560,100,2000,1,2]
["Corn silage",67,17.16,0.42900000000000005,560,100,2000,1,2]
["Corn stalks",12,40.655999999999999,0.61599999999999999,320,100,2000,1,2]
["Corn stover",30,31.32,0.45,630,100,2000,1,2]
["Corrugated cardboard",8,51.796000000000006,9.2000000000000012E-2,259,100,2000,1,2]
["Cottonseed meal",85,8.0850000000000009,1.155,800,100,2000,1,2]
["Dairy farm  'pen pack' manure",66.2,14.13,0.49,225.9,100,2000,1,2]
["Dairy farm calf hutch-pack' bedding/manure",63.77,11.81,0.43,182.4,100,2000,1,2]
["Dairy farm aisle scrap cow manure",83,6.76,0.37,321.89999999999998,100,2000,1,2]
["Dairy farm maternity pen bedding",70.099999999999994,8.07,0.27,155.5,100,2000,1,2]
["Dairy farm sawdust/woodwaste",52.95,21.26,1.01,286.2,100,2000,1,2]
["Dairy farm spoiled/rotten feed",70.099999999999994,13.33,0.68,118.1,100,2000,1,2]
["Dariy farm orts",51.67,16.72,0.85,378.9,100,2000,1,2]
["Food waste",69,11.625,0.77500000000000002,1400,100,2000,1,2]
["Fruit waste",80,11.2,0.28000000000000003,1500,100,2000,1,2]
["Grass clippings",82,10.404,0.61199999999999999,499.99999999999955,100,2000,1,2]
["Horse carcass",75,19,2.8,1700,10,1500,1,1]
["Horse manure",70,24.3,0.54,1215,100,2000,1,2]
["Laying hen litter",69,14.88,2.48,1479,100,2000,1,2]
["Leaves",38,30.132000000000001,0.55800000000000005,350,100,2000,1,2]
["Newsprint",6,58.75,9.4E-2,218,100,2000,1,2]
["Paper fiber sludge",82,9.7200000000000006,0.108,1403,100,2000,1,2]
["Paper waste",20,36.479999999999997,0.24,220,100,2000,1,2]
["Paunch manure",85,6.75,0.27,1460,100,2000,1,2]
["Poultry carcasses",65,4.2,0.84,800,100,2000,1,1]
["Rice hulls",14,31.217999999999996,0.25800000000000001,202,100,2000,1,2]
["Sawdust",39,43.92,0.1464,410,100,2000,1,2]
["Sewage sludge",78,10.648000000000001,0.96800000000000008,1700,100,2000,1,2]
["Sheep carcass",66.2,18.25,2.66,1400,10,150,1,1]
["Sheep manure",69,13.392000000000001,0.83700000000000008,1200,100,2000,1,2]
["Shrub trimmings",15,45.05,0.85,429,100,2000,1,2]
["Straw",12,49.28,0.61599999999999999,227,100,2000,1,2]
["Sugar-beet beet chips (raw)",77,32.11,0.2,216.2,100,2000,1,1]
["Swine (adult) carcass",53.3,28.87,2.59,1694,10,500,1,1]
["Swine (young pig) carcass",67.3,18.25,2.61,1773.203792,10,150,1,1]
["Swine manure",80,8.68,0.62,1200,100,2000,1,2]
["Telephone books",6,61.1,0.65799999999999992,249.99999999999997,100,2000,1,2]
["Tree trimmings",70,14.88,0.93,1296,100,2000,1,2]
["Turkey litter",26,30.783999999999999,1.9239999999999999,783,100,2000,1,2]
["Urea",0.25,0,46,431.7365269461078,13,50,1,2]
["Vegetable produce",87,6.6690000000000005,0.35100000000000003,1585,100,2000,1,2]
["Water",100,0,0,1686,100,1,2,2]
["Empty",0,0,0,0,0,2000,1,2]
[]
["# entries in materials list:",{"$f":"COUNTA(Materials)","v":49}]
[]
["Instructions for modifying the Materials Library"]
[]
["1. To permanently change the composition or cost of a material, modify data on this sheet and save the file. When a material is selected in the Optimizer Worksheet, values are copied from the Materials Library to the appropriate row in the compost mix. Cost is set as a dollar amount, the number of amount units in that cost, and whether the amount refers to a weight (lbs) or a volume (yd3)."]
["2. To insert a new material, insert a new row somewhere between the first material (i.e apple pomace) and the last row (Empty). Then enter all composition and cost data for that material.  \"Empty\" must be the last material in the library. Then, select the entire list of material names in column A and double-check to be sure that the range is still named “Materials.”  If not, use the Insert > Name >Define menu to modify the cell range for \"Materials\" so it just covers the entire list of material names."]
["3. To delete a material from the library select the row and delete.  As in #2, be sure that the named range \"Materials\" is still correctly defined."]
["4. The cell containing the # entries in the list is named \"NumMaterials\".  Do not change this variable name, or the equation in the cell."]
["5. After information in the materials library is changed, go to the Planner worksheet and reselect each of your materials to ensure that the Planner is using your new information.  Be sure to reset materials that were empty back to empty as this may have changed if you inserted or deleted materials from the library. "]
["6. All changes should be made to Materials Library “before” formulating a new compost mix.  This will ensure that your mix contains the correct values for any material characteristics you modified in the library. "]

{"sheet":"HiddenSheet","index":4,"n_rows":90,"n_cols":14,"rows_kept":90,"cut":false,"hidden":true,"merged":[[15,1,15,2],[15,3,15,4],[48,3,48,10]]}
[null,"Menu lists, Constants, and intermediates used by application"]
[]
[null,"AmountInputs list for GUI"]
[null,"lbs",1]
[null,"yds",2]
[]
[null,"MaterialConstraintsList for GUI"]
[null,"Equals constraint",1]
[null,"Range constraint",2]
[]
[null,"PerformanceConstraintsList for GUI"]
[null,"no optimization",1]
[null,"Equals constraint",2]
[null,"Range constraint",3]
[]
[null,"Material",null,"Material Units",null,"Cost Units","Constraint","Material"]
[null,"Name","Index","Index","Value","Index","Index","Type"]
[1,"Empty",49,1,{"$f":"IF(D18=IsLbs,\"lbs\",\"yds\")","v":"lbs"},1,1,2]
[2,"Empty",49,1,{"$f":"IF(D19=IsLbs,\"lbs\",\"yds\")","v":"lbs"},1,2,2]
[3,"Empty",49,1,"lbs",1,2,2]
[4,"Empty",49,1,"lbs",1,2,2]
[5,"Empty",49,1,"lbs",1,2,2]
[6,"Empty",49,1,"lbs",1,2,2]
[7,"Empty",49,1,"lbs",1,2,2]
[8,"Empty",49,1,"lbs",1,2,2]
[]
[null,"Performance Constraints"]
[null,"Constraint",null,"Index"]
[null,"Percent Moisture",null,3]
[null,"Carbon : Nitrogen",null,3]
[null,"Bulk Density (lbs/yd3)",null,1]
[null,"Water : Carbon",null,1]
[]
[]
[]
[null,"Solver "]
[null,"Materials in","Amount","Amount","Amount","Moisture","C : N","Bulk Density","Water : C","Cost"]
[null,"Compost Mix","in mix","units","lbs","%",null,"lbs/yd3",null,"$"]
[null,{"$f":"B18","v":"Empty"},0,{"$f":"E18","v":"lbs"},{"$f":"IF(B18<>\"Empty\", IF(D18=IsLbs,C39,C39*H39), 0)","v":0},{"$f":"Optimizer!H10","v":0},{"$f":"Optimizer!H54","v":0},{"$f":"Optimizer!I54","v":0},{"$f":"Optimizer!J54","v":0},{"$f":"Optimizer!K54","v":0}]
[null,"Empty",0,{"$f":"E19","v":"lbs"},0,{"$f":"Optimizer!H11","v":0},{"$f":"Optimizer!H55","v":0},{"$f":"Optimizer!I55","v":0},{"$f":"Optimizer!J55","v":0},{"$f":"Optimizer!K55","v":0}]
[null,"Empty",0,"lbs",0,{"$f":"Optimizer!H12","v":0},{"$f":"Optimizer!H56","v":0},{"$f":"Optimizer!I56","v":0},{"$f":"Optimizer!J56","v":0},{"$f":"Optimizer!K56","v":0}]
[null,"Empty",0,"lbs",0,{"$f":"Optimizer!H13","v":0},{"$f":"Optimizer!H57","v":0},{"$f":"Optimizer!I57","v":0},{"$f":"Optimizer!J57","v":0},{"$f":"Optimizer!K57","v":0}]
[null,"Empty",0,"lbs",0,{"$f":"Optimizer!H14","v":0},{"$f":"Optimizer!H58","v":0},{"$f":"Optimizer!I58","v":0},{"$f":"Optimizer!J58","v":0},{"$f":"Optimizer!K58","v":0}]
[null,"Empty",0,"lbs",0,{"$f":"Optimizer!H15","v":0},{"$f":"Optimizer!H59","v":0},{"$f":"Optimizer!I59","v":0},{"$f":"Optimizer!J59","v":0},{"$f":"Optimizer!K59","v":0}]
[null,"Empty",0,"lbs",0,{"$f":"Optimizer!H16","v":0},{"$f":"Optimizer!H60","v":0},{"$f":"Optimizer!I60","v":0},{"$f":"Optimizer!J60","v":0},{"$f":"Optimizer!K60","v":0}]
[null,"Empty",0,"lbs",0,{"$f":"Optimizer!H17","v":0},{"$f":"Optimizer!H61","v":0},{"$f":"Optimizer!I61","v":0},{"$f":"Optimizer!J61","v":0},{"$f":"Optimizer!K61","v":0}]
[null," Compost Mix Summary  ",null,null,null,{"$f":"IF(N47>0, P47/N47 * 100, 0)","v":0},{"$f":"IF(R47>0, Q47/R47, 0)","v":0},{"$f":"IF(O47>0, N47/O47, 0)","v":0},{"$f":"IF(Q47>0, P47/Q47, 0)","v":0},{"$f":"SUM(J39:J46)","v":0}]
[]
[null,"Solver Worksheet",null,"Materials"]
[null,null,null,{"$f":"B18","v":"Empty"},{"$f":"B19","v":"Empty"},{"$f":"B20","v":"Empty"},{"$f":"B21","v":"Empty"},{"$f":"B22","v":"Empty"},{"$f":"B23","v":"Empty"},{"$f":"B24","v":"Empty"},{"$f":"B25","v":"Empty"}]
[null,"Moisture","%/100",{"$f":"Optimizer!H10/100","v":0},{"$f":"Optimizer!H11/100","v":0},{"$f":"Optimizer!H12/100","v":0},{"$f":"Optimizer!H13/100","v":0},{"$f":"Optimizer!H14/100","v":0},{"$f":"Optimizer!H15/100","v":0},{"$f":"Optimizer!H117/100","v":0},{"$f":"Optimizer!H17/100","v":0}]
[null,"Carbon","%/100",{"$f":"Optimizer!I10/100","v":0},{"$f":"Optimizer!I11/100","v":0},{"$f":"Optimizer!I12/100","v":0},{"$f":"Optimizer!I13/100","v":0},{"$f":"Optimizer!I14/100","v":0},{"$f":"Optimizer!I15/100","v":0},{"$f":"Optimizer!I16/100","v":0},{"$f":"Optimizer!I17/100","v":0}]
[null,"Nitrogen","%/100",{"$f":"Optimizer!J10/100","v":0},{"$f":"Optimizer!J11/100","v":0},{"$f":"Optimizer!J12/100","v":0},{"$f":"Optimizer!J13/100","v":0},{"$f":"Optimizer!J14/100","v":0},{"$f":"Optimizer!J15/100","v":0},{"$f":"Optimizer!J16/100","v":0},{"$f":"Optimizer!J17/100","v":0}]
[null,"C:N",null,{"$f":"Optimizer!H54","v":0},{"$f":"Optimizer!H55","v":0},{"$f":"Optimizer!H56","v":0},{"$f":"Optimizer!H57","v":0},{"$f":"Optimizer!H58","v":0},{"$f":"Optimizer!H59","v":0},{"$f":"Optimizer!H60","v":0},{"$f":"Optimizer!H61","v":0}]
[null,"Bulk Density","lb/yd3",{"$f":"Optimizer!K10","v":0},{"$f":"Optimizer!K11","v":0},{"$f":"Optimizer!K12","v":0},{"$f":"Optimizer!K13","v":0},{"$f":"Optimizer!K14","v":0},{"$f":"Optimizer!K15","v":0},{"$f":"Optimizer!K16","v":0},{"$f":"Optimizer!K17","v":0}]
[null,null,"Units",{"$f":"D39","v":"lbs"},{"$f":"D40","v":"lbs"},{"$f":"D41","v":"lbs"},{"$f":"D42","v":"lbs"},{"$f":"D43","v":"lbs"},{"$f":"D44","v":"lbs"},{"$f":"D45","v":"lbs"},{"$f":"D46","v":"lbs"}]
[null,null,null,0]
[null,"Solver inputs","lbs or yds",{"$f":"Amount1","v":0},{"$f":"Amount2","v":0},{"$f":"Amount3","v":0},{"$f":"Amount4","v":0},{"$f":"Amount5","v":0},{"$f":"Amount6","v":0},{"$f":"Amount7","v":0},{"$f":"Amount8","v":0},null,"Batch totals"]
[null,"Materials","lbs",{"$f":"IF(D18=IsLbs,D58,D58*D55)","v":0},{"$f":"IF(D19=IsLbs,E58,E58*E55)","v":0},{"$f":"IF(D20=IsLbs,F58,F58*F55)","v":0},{"$f":"IF(D21=IsLbs,G58,G58*G55)","v":0},{"$f":"IF(D22=IsLbs,H58,H58*H55)","v":0},{"$f":"IF(D23=IsLbs,I58,I58*I55)","v":0},{"$f":"IF(D24=IsLbs,J58,J58*J55)","v":0},{"$f":"IF(D25=IsLbs,K58,K58*K55)","v":0},"size",{"$f":"SUM(D59:K59)","v":0},"lbs"]
[null,null,"yds",{"$f":"IF(D55>0, D59/D55, 0)","v":0},{"$f":"IF(E55>0, E59/E55, 0)","v":0},0,0,0,0,0,0]
[null,"Cost","$/lb",{"$f":"Optimizer!T54/2000","v":0},{"$f":"Optimizer!T55/2000","v":0},{"$f":"Optimizer!T56/2000","v":0},{"$f":"Optimizer!T57/2000","v":0},{"$f":"Optimizer!T58/2000","v":0},{"$f":"Optimizer!T59/2000","v":0},{"$f":"Optimizer!T60/2000","v":0},{"$f":"Optimizer!T61/2000","v":0},"cost",{"$f":"SUMPRODUCT($D$59:$K$59,D61:K61)","v":0},"$"]
[null,"Carbon","lbs",{"$f":"D52*D59","v":0},{"$f":"E52*E59","v":0},0,0,0,0,0,0,{"$f":"B62","v":"Carbon"},{"$f":"SUM(D62:K62)","v":0},"lbs"]
[null,"Nitrogen","lbs",{"$f":"D53*D59","v":0},{"$f":"E53*E59","v":0},0,0,0,0,0,0,{"$f":"B63","v":"Nitrogen"},{"$f":"SUM(D63:K63)","v":0},"lbs"]
[]
[null,"Preformance Constraints"]
[null,null,null,{"$f":"B51","v":"Moisture"},null,null,{"$f":"B55","v":"Bulk Density"}]
[null,"Min Constraint",{"$f":"SUMPRODUCT($D$59:$K$59,D51:K51)/M59*100","v":{"$error":"#DIV/0!"}},"GE",{"$f":"IF(MoistureConstraint=3, MoistureMinConstraint, \"\")","v":40},{"$f":"IF(SUM(D60:K60)>0,SUMPRODUCT(D55:K55,D59:K59)/M59,0)","v":0},"GE",{"$f":"IF(BulkDensityConstraint=3, BulkDensityMinConstraint, \"\")","v":""}]
[null,"Equals Constraint",null,"EQ",{"$f":"IF(MoistureConstraint=2, MoistureEqualsConstraint, \"\")","v":""},null,"EQ",{"$f":"IF(BulkDensityConstraint=2, BulkDensityEqualsConstraint, \"\")","v":""}]
[null,"Max Constraint",null,"LE",{"$f":"IF(MoistureConstraint=3, MoistureMaxConstraint, \"\")","v":60},null,"LE",{"$f":"IF(BulkDensityConstraint=3, BulkDensityMaxConstraint, \"\")","v":""}]
[null,"% Moisture Constraints",null,null,null,null,null,null,null,null,null,null,{"$f":"B70","v":"% Moisture Constraints"}]
[null,"Min Constraint",null,{"$f":"D51-MoistureMinConstraint/100","v":-0.4},-0.4,-0.4,-0.4,-0.4,-0.4,-0.4,-0.4,{"$f":"SUMPRODUCT(D71:K71,D59:K59)","v":0},"GE",0]
[null,"Equals Constraint",null,{"$f":"D51-MoistureEqualsConstraint/100","v":0},0,0,0,0,0,0,0,{"$f":"SUMPRODUCT(D72:K72,D59:K59)","v":0},"EQ",0]
[null,"Max Constraint",null,{"$f":"D51-MoistureMaxConstraint/100","v":-0.6},-0.6,-0.6,-0.6,-0.6,-0.6,-0.6,-0.6,{"$f":"SUMPRODUCT(D73:K73,D59:K59)","v":0},"LE",0]
[null,"Bulk Density Constraints",null,null,null,null,null,null,null,null,null,null,{"$f":"B74","v":"Bulk Density Constraints"}]
[null,"Min Constraint",null,{"$f":"D59-BulkDensityMinConstraint*D60","v":0},0,0,0,0,0,0,0,{"$f":"SUMPRODUCT(D75:K75,D59:K59)","v":0},"GE",0]
[null,"Equals Constraint",null,{"$f":"D59-BulkDensityEqualsConstraint*D60","v":0},0,0,0,0,0,0,0,{"$f":"SUMPRODUCT(D76:K76,D59:K59)","v":0},"EQ",0]
[null,"Max Constraint",null,{"$f":"D59-BulkDensityMaxConstraint*D60","v":0},0,0,0,0,0,0,0,{"$f":"SUMPRODUCT(D77:K77,D59:K59)","v":0},"LE",0]
[null,"C:N Constraints",null,null,null,null,null,null,null,null,null,null,{"$f":"B78","v":"C:N Constraints"}]
[null,"Min Constraint",null,{"$f":"D52-CNMinConstraint*D53","v":0},0,0,0,0,0,0,0,{"$f":"M62-CNMinConstraint*M63","v":0},"GE",0]
[null,"Equals Constraint",null,{"$f":"D52-CNEqualsConstraint*D53","v":0},0,0,0,0,0,0,0,{"$f":"M62-CNEqualsConstraint*M63","v":0},"EQ",0]
[null,"Max Constraint",null,{"$f":"D52-CNMaxConstraint*D53","v":0},0,0,0,0,0,0,0,{"$f":"M62-CNMaxConstraint*M63","v":0},"LE",0]
[null,"Materials Constraints"]
[null,null,null,{"$f":"B39","v":"Empty"},null,null,{"$f":"B40","v":"Empty"},null,null,{"$f":"B41","v":"Empty"},null,null,{"$f":"B42","v":"Empty"}]
[null,"Min Constraint",{"$f":"IF(Amount1Constraint=2, D59, \"\")","v":""},"GE",{"$f":"IF(Amount1Constraint=2, IF(D18=1,Mat1MinConstraint,Mat1MinConstraint*E55), \"\")","v":""},{"$f":"IF(Amount2Constraint=2, E59, \"\")","v":0},"GE",{"$f":"IF(Amount2Constraint=2, IF(D19=1, Mat2MinConstraint, Mat2MinConstraint*E55), \"\")","v":0},{"$f":"IF(Amount3Constraint=2, F59, \"\")","v":0},"GE",{"$f":"IF(Amount3Constraint=2, IF(D20=1, Mat3MinConstraint, Mat3MinConstraint*F55), \"\")","v":0},{"$f":"IF(Amount4Constraint=2, G59, \"\")","v":0},"GE",{"$f":"IF(Amount4Constraint=2, IF(D21=1, Mat4MinConstraint, Mat4MinConstraint*G55), \"\")","v":0}]
[null,"Equals Constraint",{"$f":"IF(Amount1Constraint=1, D59, \"\")","v":0},"EQ",{"$f":"IF(Amount1Constraint=1,  IF(D18=1,Mat1EqualsConstraint,Mat1EqualsConstraint*E55), \"\")","v":1000},{"$f":"IF(Amount2Constraint=1, E59, \"\")","v":""},"EQ",{"$f":"IF(Amount2Constraint=1, IF(D19=1, Mat2EqualsConstraint, Mat2EqualsConstraint*E55), \"\")","v":""},{"$f":"IF(Amount3Constraint=1, F59, \"\")","v":""},"EQ",{"$f":"IF(Amount3Constraint=1, IF(D20=1, Mat3EqualsConstraint, Mat3EqualsConstraint*G55), \"\")","v":""},{"$f":"IF(Amount4Constraint=1, G59, \"\")","v":""},"EQ",{"$f":"IF(Amount4Constraint=1, IF(D21=1, Mat4EqualsConstraint, Mat4EqualsConstraint*G55), \"\")","v":""}]
[null,"Max Constraint",{"$f":"IF(Amount1Constraint=2, D59, \"\")","v":""},"LE",{"$f":"IF(Amount1Constraint=2,  IF(D18=1,Mat1MaxConstraint,Mat1MaxConstraint*E55), \"\")","v":""},{"$f":"IF(Amount2Constraint=2, E59, \"\")","v":0},"LE",{"$f":"IF(Amount2Constraint=2,  IF(D19=1, Mat2MaxConstraint, Mat2MaxConstraint*E55), \"\")","v":20000},{"$f":"IF(Amount3Constraint=2, F59, \"\")","v":0},"LE",{"$f":"IF(Amount3Constraint=2,  IF(D20=1, Mat3MaxConstraint, Mat3MaxConstraint*F55), \"\")","v":20000},{"$f":"IF(Amount4Constraint=2, G59, \"\")","v":0},"LE",{"$f":"IF(Amount4Constraint=2,  IF(D21=1, Mat4MaxConstraint, Mat4MaxConstraint*G55), \"\")","v":20000}]
[null,null,null,{"$f":"B43","v":"Empty"},null,null,{"$f":"B44","v":"Empty"},null,null,{"$f":"B45","v":"Empty"},null,null,{"$f":"B46","v":"Empty"}]
[null,"Min Constraint",{"$f":"IF(Amount5Constraint=2, H59, \"\")","v":0},"GE",{"$f":"IF(Amount5Constraint=2, IF(D22=1,Mat5MinConstraint,Mat5MinConstraint*H59), \"\")","v":0},{"$f":"IF(Amount6Constraint=2, I59, \"\")","v":0},"GE",{"$f":"IF(Amount6Constraint=2, IF(D23=1, Mat6MinConstraint, Mat6MinConstraint*I55), \"\")","v":0},{"$f":"IF(Amount7Constraint=2, J59, \"\")","v":0},"GE",{"$f":"IF(Amount7Constraint=2, IF(D24=1, Mat7MinConstraint, Mat7MinConstraint*J55), \"\")","v":0},{"$f":"IF(Amount8Constraint=2, K59, \"\")","v":0},"GE",{"$f":"IF(Amount8Constraint=2, IF(D25=1, Mat8MinConstraint, Mat8MinConstraint*K55), \"\")","v":0}]
[null,"Equals Constraint",{"$f":"IF(Amount5Constraint=1, H59, \"\")","v":""},"EQ",{"$f":"IF(Amount5Constraint=1,  IF(D22=1,Mat5EqualsConstraint,Mat5EqualsConstraint*H59), \"\")","v":""},{"$f":"IF(Amount6Constraint=1, I59, \"\")","v":""},"EQ",{"$f":"IF(Amount6Constraint=1, IF(D23=1, Mat6EqualsConstraint, Mat6EqualsConstraint*I55), \"\")","v":""},{"$f":"IF(Amount7Constraint=1, J59, \"\")","v":""},"EQ",{"$f":"IF(Amount7Constraint=1, IF(D24=1, Mat7EqualsConstraint, Mat7EqualsConstraint*J55), \"\")","v":""},{"$f":"IF(Amount8Constraint=1, K59, \"\")","v":""},"EQ",{"$f":"IF(Amount8Constraint=1, IF(D25=1, Mat8EqualsConstraint, Mat8EqualsConstraint*K55), \"\")","v":""}]
[null,"Max Constraint",{"$f":"IF(Amount5Constraint=2, H59, \"\")","v":0},"LE",{"$f":"IF(Amount5Constraint=2,  IF(D22=1,Mat5MaxConstraint,Mat5MaxConstraint*H59), \"\")","v":20000},{"$f":"IF(Amount6Constraint=2, I59, \"\")","v":0},"LE",{"$f":"IF(Amount6Constraint=2,  IF(D23=1, Mat6MaxConstraint, Mat6MaxConstraint*I55), \"\")","v":20000},{"$f":"IF(Amount7Constraint=2, J59, \"\")","v":0},"LE",{"$f":"IF(Amount7Constraint=2,  IF(D24=1, Mat7MaxConstraint, Mat7MaxConstraint*J55), \"\")","v":20000},{"$f":"IF(Amount8Constraint=2, K59, \"\")","v":0},"LE",{"$f":"IF(Amount8Constraint=2,  IF(D25=1, Mat8MaxConstraint, Mat8MaxConstraint*K55), \"\")","v":20000}]

{"sheet":"Documentation","index":5,"n_rows":93,"n_cols":3,"rows_kept":93,"cut":false,"hidden":true,"merged":[]}
[null,"Technical Documentation for Spartan Compost Optimizer"]
[]
[null,"Title"]
[null,"Spartan Compost Optimizer, An Excel spreadsheet that simplifies planning and optimizing compost recipes."]
[]
[null,"Authors"]
[null,"Principal Investigator: Dr. Dale Rozeboom, 2209I Anthony Hall, Department of Animal Science, Michigan State University, East Lansing, MI  48824.  e-mail: rozeboom@msu.edu"]
[null,"Programming: Robert Kriegel M.S., 3270B Anthony Hall, Department of Animal Science, Michigan State University, East Lansing, MI  48824.  e-mail: kriegelr@msu.edu"]
[]
[null,"Purpose"]
[null,"Spartan Compost Optimizer is a Microsoft® Office Excel application used to plan compost mixes.  It allows the user to automatically or manually optimize a compost recipe to meet performance and materials constraints while minimizing cost."]
[]
[null,"Copyright"]
[null,"Copyright 2013 © Michigan State University Board of Trustees.  All Rights Reserved."]
[]
[null,"Filename"]
[null,"Unprotected filename: Spartan_CompostOptimizer v1_00.xls"]
[null,"Protected filename: Spartan_CompostOptimizer.xls"]
[]
[null,"File Protection Password"]
[null,"Swine2Dirt"]
[]
[null,"Design goals"]
[null,"1.  Implement a MS Excel spreadsheet that simplifies the process of planning a compost mix."]
[null,"2.  Allow users to optimze a compost recipe to meet constraints on performance criteria and materials usage while minimizing the materials cost of the mix."]
[null,"3.  Implement a solution that promotes cost effective composting of animal waste on livestock and poultry operations, yet meets the compost planning needs of a much wider audience."]
[null,"4.  Use Excel's solver to automate the process of finding an optimal solution.  Existing spreadsheets force the user to manually set amounts for materials in a compost mix.  This trial and error process is time consuming, confusing to some users, and does not provide the user with adequate information to reduce materials costs.  Leveraging the power of Excel's solver will increase the efficiency of composting by saving the user's time, simplify planning, and allow users to better manage materials costs.  The automated optimization is a novel contribution to this problem space."]
[null,"5.  Package the solution in a user interface that is simple to use, intuitive, yet flexible enough to meet the needs of very diverse clientele and usages."]
[]
[null,"Specifications"]
[null,"1.  The user work flow process being modeled involves the following steps:"]
[null,"     a.  Select materials to be used in a compost mix from a library, adjust material composition and cost as needed."]
[null,"     b.  Set optimization constraints on all materials used in the mix.  Program needs to support equals and range constraints."]
[null,"     c.  Set optimization constraints on some or all performance criteria.  As with materials, support both equals and range constraints."]
[null,"     d.  Modify the amounts of each material until the mix meets performance and materials constraints."]
[null,"     e.  Print a batch sheet containing the amounts of all materials in the compost mix.  This report will be used by the person who actually makes the compost mix.  It may also be saved to document business practices."]
[null,"2.  Include a library of materials with a complete set of book values for material compositions and costs."]
[null,"3.  The user needs to be able to make quick, temporarily changes to the composition of materials in a compost mix.  They also need to be able to make less frequent but permanent changes to the materials library.  Library changes include changing a material's composition and cost, and adding/deleting materials from the library."]
[null,"4.  Percent moisture of a material changes far more frequently than other composition characteristics."]
[null,"5.  Allow the user to work with individual material amounts on either a weight (lb) or volume (cubic yard) basis."]
[null,"6.  Allow the user to work with individual material costs on either a weight (lb) or volume (cubic yard) basis."]
[null,"7.  Make it very easy to select materials used in a mix.  PI would prefer to use drop down list components to select materials."]
[null,"8.  Support up to 8 materials in a mix."]
[null,"9.  Program should support performance constraints for percent moisture, carbon to nitrogen ratio, bulk density, and water to carbon ratio.  PI has specified default settings for performance constraints."]
[null,"10.  Since user can change default performance criteria, application needs a single step method to reset all performance constraints back to their default settings."]
[null,"11.  Spreadsheet needs to support both manual and automatic optimization of a compost mix."]
[null,"12.  Step d of the work flow process should be organized into simple table(s) where users adjust material amounts, view compost mix composition and summary information, and review feedback of additional changes required to meet constraints."]
[null,"13.  Automatic Optimization:"]
[null,"     a.  To the user, automatic optimization should involve simply pressing a button at step d in the work flow process."]
[null,"     b.  The Excel solver should be parameterized to adjust material amounts to minimize total compost mix cost while satisfying all user specified performance and materials constraints."]
[null,"     c.  Once Solver optimization is complete, material amount results should be written back to the table indicated in #11 in the appropriate units (lbs or yds3) ."]
[null,"     d.  Intermediate calculations needed by the solver that do not add value for the user should be hidden from view."]
[]
[null,"Implementation Details"]
[null,"Materials Library"]
[null,"1.1  This worksheet contains the materials library.  All material composition and cost information is data, no calculations.  "]
[null,"1.2  The range containing the list of material names is the variable named Materials.  This list is used to drive combo box controls in Planner.  If a user enters or deletes a row from the list, Excel typically updates the range of the variable without further attention."]
[null,"1.3  The last entry in the materials list MUST be \"Empty\"."]
[null,"1.4  Two lines below the end of the materials list is a cell named NumMaterials.  This cell contains an equation that returns the number of entries in the materials list.  This value is used by macros to determined if a material is set to empty."]
[null,"1.5  Material cost units are an integer value or 1 for lbs and 2 for cubic yards."]
[]
[null,"HiddenSheet"]
[null,"2.1  The HidddenSheet worksheet contains everything that we don't need the user to see.  This includes text lists used in the application's drop down menus, intermediates, and named constrants."]
[null,"2.2  The sheet contains a table of materials information used by the user interface:"]
[null,"     a.  When a user selects a material, the name of that material is copied from the Materials library and pasted into the Material Name column."]
[null,"     b.  The Material Index column is the cell link for the materials combo box controls on Planner worksheet."]
[null,"     c.  The Material Units Index column is the cell link for the material units combo box controls on Planner worksheet.",0]
[null,"     d.  The Material Units Value column contains equations to converts a Material Units Index into \"lbs\" or \"yds\".  This information is displayed in multiple locations on the Planner worksheet."]
[null,"     e.  The Cost Units Index column is the cell link for the combo box controls for material cost units."]
[null,"     f.  The Constraint index column contains cell links for material constraint combo box controls."]
[null,"2.3  The performance constraints table contains cell links for combo box controls for performance constraints from the Planner worksheet."]
[]
[null,"Planner"]
[null,"3.1  This worksheet is the main user interface where users select materials for a compost mix, set performance and materials constraints, and optimize a mix."]
[null,"3.2  The worksheet is organized from top to bottom into four main steps reflecting the perceived workflow used to plan a compost mix."]
[null,"3.3  All of the combo box controls used here have cell links set to cells in the HiddenSheet worksheet."]
[null,"3.4  Worksheet columns O to U are hidden from the user.  These contain a table of intermediate compost mix calculations used by the solver.  They also contain a box where the solver model is output for debugging purposes."]
[null,"Step 1.  This table only contains combo box controls and data cells.  When a user selects a material using a combo box control, a macro is triggered that copies that material's composition and cost information from the Materials library to this worksheet.  The macros used in this process are in Module \"Module1\".  The key subroutine is PasteMaterial(Index).  Note that this macro is sensitive to the cell names and absolute location of cells in the Materials, HiddenSheet, and Planner worksheets.  Refer to detailed documentation in the macro itself if worksheets are rearranged."]
[null,"Step 2.  This table allows the user to set and modify constraints on materials.  The table contains a combination of calculated cells, user entries for constraint values, and a combo box for selecting the type of constraint."]
[null,"Step 3.  This table allows the user to set and modify performance constraints.  The table contains a combination of calculated cells, user entries for constraint values, and a combo box for selecting the type of constraint.  It functions very similarly to the table in step 2."]
[null,"This step also contains a command button labeled \"Reset Performance Constraints to Default Values\".  Pressing this button triggers a macro named PerformanceDefaultsButton_Click() in Sheet1 (Planner) that resets values in the table to their design time values."]
[null,"Step 4.  This section contains three tables and a button as follows:"]
[null,"     a.  The compost mix table allows users to enter amounts for each of the materials in the compost mix.  The cell range is named MixInputs.  The same range is also used by the solver to optimize the mix.  The rest of the table contains calculated performance outputs for each material, and for the overall mix."]
[null,"     b.  The performance summary table contains calculated outputs that provide the user with feedback on how the compost mix meets performance constraints, and offers suggestions on how to improve the mix."]
[null,"     c.  The materials summary table contains calculated outputs that provide the user with feedback on how the compost mix meets materials constraints, and offers suggestions on how to improve the mix."]
[null,"     d.  The user presses the \"Optimize Compost Mix\" command button to launch a macro that optimizes the compost mix.  The solver solution is written to the same named range (MixAmounts) where users manually enter material amounts.  The macro, named OptimizeMixButton_Click(), is in Sheet1 (Planner).  This macro resets the Solver, sets Solver options, adds performance constraints, adds materials constraints, and executes the solver.  For debugging and evaluation, the solver model is output to the Solver Model block on the Planner worksheet.  For additional details see documentation in the macro itself.  For details on programming optimizations in Excel search the Visual Basic editor help using the keyword \"Solver\".  The solver routines used here include SolverReset, SolverOptions, SolverOK, SolverAdd, SolverSolve, and SolverSave."]
[null,"     e.  For testing purposes a second command buton labeld Optimize Mixs Using Simplex' was added.  This button uses a different macro and some different constraint equations.  Roy Black suggested that constraint equations for performance ratios (C:N, Water:C) be modified so they satisfy a linear model.  This allows us to force Solver to use the Simplex method.  It also takes advantage of the reality that linear optimization models are more predicitable, i.e. more likely to converge to an answer.  Later, one of the two command buttons will be removed."]
[]
[null,"Report"]
[null,"4.1  The Report worksheet uses information from the Planner worksheet to construct a batch sheet that the user can print and take with them when they prepare the compost mix."]
[null,"4.2  Page setup for this worksheet is landscape format, fitted to 1 by 1 page."]
[null,"4.3  Font sizes were selected so results are written large, in an easy to read font."]
[null,"4.4  This sheet should be left unprotected so the user can customize the report as they see fit."]