# Optimizing the Loading of a Limited Compartment

This is a classic Solver problem with many possible variations. Knapsack problems involve selecting the correct items to load into a compartment which is limited (Constrained) in some way such as by its size or maximum weight of its load. Objects selected for loading must maximize or minimize a given criterion while at the same time staying within the Constraints of the compartment.

These type of optimization problems are known as Knapsack Problems because of the well known classic example of selecting the correct items to optimally fill a camper’s knapsack. The knapsack has a limited weight-carrying capacity and items are selected that optimize at least one criterion while not exceeding the knapsack’s weight-carrying capacity.

A knapsack is being loaded for a camping trip. This knapsack has a maximum weight-carrying limit and a maximum load size limit. The camper can choose from 4 different food items to put into the knapsack. The selected items must maximize the overall number calories and provide at least a minimum number of grams of protein while not exceeding the maximum load size and weight-carry capacity of the knapsack.

The knapsack’s load cannot exceed a weight of 10 kilograms or a volume of 0.125 m3. The load of food items must contain at least 200 grams of protein.

The load may contain any number of each of the 4 following food items:

- Candy Bar

- Sandwich

- Can of Juice

- Apple

Specific information about each food items are as follows:

(Click on Image To See a Larger Version)

## Excel Solver Problem Solving Steps

### Excel Solver Step 1 – Determine the Objective

In this case, the objective is to maximize the calories in the load. The cell calculating the sum total number of calories is the Objective Cell.

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

• Histograms in Excel
• Bar Chart in Excel
• Combinations & Permutations in Excel
• Normal Distribution in Excel
• t-Distribution in Excel
• Binomial Distribution in Excel
• z-Tests in Excel
• t-Tests in Excel
• Hypothesis Tests of Proportion in Excel
• Chi-Square Independence Tests in Excel
• Chi-Square Goodness-Of-Fit Tests in Excel
• F Tests in Excel
• Correlation in Excel
• Pearson Correlation in Excel
• Spearman Correlation in Excel
• Confidence Intervals in Excel
• Simple Linear Regression in Excel
• Multiple Linear Regression in Excel
• Logistic Regression in Excel
• Single-Factor ANOVA in Excel
• Two-Factor ANOVA With Replication in Excel
• Two-Factor ANOVA Without Replication in Excel
• Randomized Block Design ANOVA in Excel
• Repeated-Measures ANOVA in Excel
• ANCOVA in Excel
• Normality Testing in Excel
• Nonparametric Testing in Excel
• Post Hoc Testing in Excel
• Creating Interactive Graphs of Statistical Distributions in Excel
• Solving Problems With Other Distributions in Excel
• Optimization With Excel Solver
• Chi-Square Population Variance Test in Excel
• Analyzing Data With Pivot Tables
• SEO Functions in Excel
• Time Series Analysis in Excel
• VLOOKUP