How to Determine the Price per Pound Using Excel Formula

The price per pound is a common way to compare the cost of different items that are sold by weight. It is calculated by dividing the total price by the total weight in pounds. For example, if a bag of apples costs $4.50 and weighs 3 pounds, the price per pound is $4.50 / 3 = $1.50.

In Excel, we can use a simple formula to calculate the price per pound for any item. The formula is:

=price / weight

where price is the cell that contains the total price, and weight is the cell that contains the total weight in pounds. This formula will return the price per pound as a decimal number. To format it as a currency, we can use the Format Cells dialog box and choose the Currency category.

Procedures

To determine the price per pound using Excel formula, we need to follow these steps:

1. Enter the total price and the total weight in pounds for each item in separate cells. For example, we can enter them in columns A and B, starting from row 2.
2. In the cell where we want to display the price per pound, enter the formula =price / weight, replacing price and weight with the appropriate cell references. For example, in cell C2, we can enter =A2 / B2.
3. Copy the formula down to the other cells in column C, or use the fill handle to drag it down.
4. Select the cells in column C, and open the Format Cells dialog box by pressing Ctrl + 1 or right-clicking and choosing Format Cells.
5. In the Format Cells dialog box, choose the Currency category, and select the desired currency symbol, decimal places, and negative number format. Click OK to apply the formatting.

Explanation

To understand how the formula works, let’s look at an example. Suppose we have the following data in columns A and B:

We want to calculate the price per pound for each item in column C. To do this, we use the formula =price / weight in cell C2, and copy it down to the other cells in column C. The formula in cell C2 is =A2 / B2, which means:

=price / weight =4.50 / 3 =1.50

This means that the price per pound for apples is $1.50. Similarly, the formula in cell C3 is =A3 / B3, which means:

=price / weight =3.00 / 2 =1.50

This means that the price per pound for bananas is also $1.50. Finally, the formula in cell C4 is =A4 / B4, which means:

=price / weight =2.40 / 1.5 =1.60

This means that the price per pound for carrots is $1.60. After applying the currency formatting, the result will look like this:

Scenario

Let’s use a scenario to illustrate how to determine the price per pound using Excel formula. Suppose we are shopping for groceries and we want to compare the cost of different types of cheese. We have the following data in columns A and B:

We want to calculate the price per pound for each cheese in column C. However, we notice that the weight is given in ounces, not pounds. To convert ounces to pounds, we need to divide by 16, since there are 16 ounces in a pound. Therefore, we need to modify the formula to:

=price / (weight / 16)

This is equivalent to multiplying the weight by 16 before dividing by the price. In cell C2, we enter the formula =A2 / (B2 / 16), and copy it down to the other cells in column C. The formula in cell C2 is =A2 / (B2 / 16), which means:

=price / (weight / 16) =5.99 / (8 / 16) =5.99 / 0.5 =11.98

This means that the price per pound for cheddar cheese is $11.98. Similarly, the formula in cell C3 is =A3 / (B3 / 16), which means:

=price / (weight / 16) =6.49 / (7 / 16) =6.49 / 0.4375 =14.83

This means that the price per pound for swiss cheese is $14.83. Finally, the formula in cell C4 is =A4 / (B4 / 16), which means:

=price / (weight / 16) =4.29 / (6 / 16) =4.29 / 0.375 =11.44

This means that the price per pound for mozzarella cheese is $11.44. After applying the currency formatting, the result will look like this:

Result

From the scenario, we can see that the cheapest cheese per pound is mozzarella, followed by cheddar, and then swiss. We can use this information to make our purchasing decision based on our budget and preference.