How to Calculate Unit Price in Excel (3 Quick Methods)

In most cases, there are a lot of advantages to knowing the unit price beforehand. When you have a tool like Microsoft Excel, you can easily perform this kind of task in bulk and with very quick methods. This article demonstrates how to calculate unit price in Excel.

Download Practice Workbook

You can download the practice workbook from the link below.

What Is Unit Price?

Unit Price is the price at which one unit of a product or service is exchanged between the producer, manufacturer or service provider and the consumer or customer. It is very important to calculate this particular type of cost beforehand as it helps a lot in business, especially in marketing. In other words, you can also say Unit Price is the sum of the Unit Cost Price and Profit Margin Per Unit.

Arithmetic Formula to Calculate Unit Price

To calculate the Unit Price you can use the following arithmetic formula:

UP=(Total Cost + Profit Margin)/Total Units

In this formula:

• UP = Unit price of the product or service.
• Total Cost = Total cost for all the units of products or services.
• Profit Margin = Total profit margin for all the units of product service.
• Total Units = Total number of units of the product of service.

3 Methods to Calculate Unit Price in Excel

Suppose you have a dataset of a stationery shop with a list of Commodities along with their Total Cost, Profit Margin, and Units. Now you want to calculate their Unit Price. Here, I will show you 3 methods to do so.

1. Inserting Arithmetic Formula Manually to Calculate Unit Price

Calculating Unit Price by inserting the arithmetic formula manually in Excel is a very convenient method. Now, follow the steps below to do so.

Steps:

• First, add a column for Unit Price.
• Next, select cell F5 and insert the following formula.

Here, cell F5 is the first cell of the column Unit Price. Cell C5, D5, and E5 are the first cells in the column Total Cost, Profit Margin, and Units respectively.

• Finally, drag the Fill Handle to the rest of the cells in the column.

Read More: How to Calculate Cost per Unit in Excel (With Easy Steps)

Similar Readings

• How to Calculate Price Increase Percentage in Excel (3 Easy Ways)
• Calculate Price Per Pound in Excel (3 Easy Ways)
• How to Calculate Coupon Rate in Excel (3 Ideal Examples)
• Calculate Retail Price in Excel (2 Suitable Ways)
• How to Calculate Weighted Average Price in Excel (3 Easy Ways)

2. Calculating Rounded Unit Price in Excel

Let’s assume at this point that after you have calculated your Unit Price in the method mentioned above, you now want your results rounded up to zero decimal places. To do so, you can use the ROUNDUP Function in Excel. Now, follow the steps below to get your Unit Price (Rounded).

Steps:

• First, add a column for the Unit Price (Rounded).
• At this point, select cell G5 and insert the following formula.

Here, cell F5 is the first cell of the column Unit Price (Rounded). The arguments of the ROUNDUP function are number and num_digits respectively.

• Lastly, drag the Fill handle for the rest of the cells in the column.

Read More: How to Calculate Selling Price Per Unit in Excel (3 Easy Ways)

3. Using VBA Code to Calculate Unit Price in Excel

Using VBA code, you can easily create a function to calculate Unit Price and later use that function. Now, I will show you two sets of steps. Here, the first set of steps will create a UnitPrice function, and the next set of steps will utilize this function.

Steps 01:

• First, press ALT + F11 to open the VBA
• Now, select Sheet 3 and Right-Click on it.
• Next, sequentially select Insert > Module.

• After that, copy the following code and paste it into the blank space.

Public Function UnitPrice(Total_Cost As Double, Profit_Margin As Double, Units As Double)
A = Total_Cost
B = Profit_Margin
C = Units
UnitPrice = (A + B) / C
End Function

Steps 02:

• Now, since you have created a function UnitPrice, add a column for Unit Price.
• Next, select cell F5 and insert the following formula.

Here the arguments of the UnitPrice function are Total_Cost, Profit_Margin, and Units respectively.

• Finally, drag the Fill handle for the rest of the cells in the column.

Read More: How to Calculate Variable Cost Per Unit in Excel (with Quick Steps)

Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.

Related Articles

• How to Calculate Average Price in Excel (7 Useful Methods)
• Calculate Average Selling Price in Excel (3 Easy Methods)
• How to Calculate Price Per Square Meter in Excel (3 Handy Methods)
• Calculate Selling Price in Excel (4 Easy Methods)
• How to Calculate Bond Price in Excel (4 Simple Ways)
• Calculate Projected Cost in Excel (4 Effective Ways)
• How to Calculate Discount Price in Excel (4 Quick Methods)