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

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to know how to calculate selling price per unit in Excel? Then, this is the right place for you. In regular life, we have to calculate different parameters, especially purchasing various products. Sometimes we need to calculate the selling price per unit for those products. Here, you will find 3 different step-by-step explained ways to calculate selling price per unit in Excel.


Download Practice Workbook


What Are Margin and Markup?

The profit made from the selling of a product or service is known as the sales Margin.

It’s used to look at earnings at the level of a single sale transaction rather than a whole organization.

The equation for calculating Margin,

Margin (%) = (Selling Price - Cost) / Selling Price

The amount by which the cost of a product is increased to get at the selling price is known as Markup.

The equation for calculating Markup,

Markup (%) = (Selling Price - Cost) / Cost

The parameters used to calculate Margin and Markup are the same, but they return different values. The basis of Markup is cost and for Margin it is revenue. So, when the cost is changed over time, the Markup value is more likely to change than the Margin value.


What Is Selling Price Per Unit?

Selling Price is the final price of any product or the price at which any customer will buy the product. Selling Price Per Unit is the price of 1 unit of that product.

The equation for calculating Selling Price Per Unit,

Selling Price Per Unit = Total Selling Price / No of Products

3 Ways to Calculate Selling Price Per Unit in Excel

Here, we have the following dataset containing the No and Cost of some Products. We will calculate the selling price per unit of these products by using the following methods.

Calculating Selling Price Per Unit in Excel


1. Calculating Selling Price Per Unit with Margin

For the first method, we will calculate the selling price per unit with Margin. Here, for the dataset, the Margin value is 20%.

Calculating Selling Price Per Unit with Margin

Follow the steps given below to do it on your own.

Steps:

  • In the beginning, select Cell E5.
  • Then, insert the following formula.
=D5/C5

Here, we divided Cost of the products by No of Product to get the Cost Per Unit.

  • Now, press ENTER.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Calculating Selling Price Per Unit with Margin

  • Thus, you will get the Cost Per Unit of these products.

Calculating Selling Price Per Unit with Margin

  • Next, select Cell F5.
  • Then, insert the following formula.
=E5/(1-$C$11)

Here, first, we Subtracted the Margin value from 1 (Here, 1 means 100%) then we divided the Cost Per Unit by the subtracted Margin to get the value of the Selling Price Per Unit.

  • After that, press ENTER.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Calculating Selling Price Per Unit with Margin

  • Finally, you will get the Selling Price Per Unit for the products using Margin.

Calculating Selling Price Per Unit with Margin

Read More: How to Calculate Selling Price from Cost and Margin in Excel


Similar Readings


2. Using Markup to Calculate Selling Price Per Unit

We can also calculate the selling price per unit using the Markup value. Here, for the dataset, the Markup value is given as 15%.

Using Markup to Calculate Selling Price Per Unit

Go through the following steps to calculate the selling price per unit for your dataset.

Steps:

  • First, select Cell E5.
  • After that, insert the following formula.
=D5/C5

Here, we divided the Cost of the products by No of Product to get the Cost Per Unit.

  • Now, press ENTER.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Using Markup to Calculate Selling Price Per Unit

  • Thus, you will get the Cost Per Unit of the products.

Using Markup to Calculate Selling Price Per Unit

  • Now, select Cell F5.
  • Then, insert the following formula.
=E5*(1+$C$11)

Here, first, we Added the Markup value with 1 (Here, 1 means 100%) then we multiplied the Cost Per Unit with the added Markup to get the value of the Selling Price Per Unit.

  • After that, press ENTER.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Using Markup to Calculate Selling Price Per Unit

  • Finally, you will get the Selling Price Per Unit of the products using Markup.

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


3. Use of Generic Formula to Calculate Selling Price Per Unit

Finally, we can also calculate the selling price per unit using the Generic Formula. Here, we have a dataset of Products containing the No of Products and Total Selling Price. We will calculate the Selling Price Per Unit of these products using the Generic Formula.

Use of Generic Formula to Calculate Selling Price Per Unit

Follow the steps to do it on your own.

Steps:

  • In the beginning, select Cell E5.
  • After that, insert the following formula.
=D5/C5

Here, we divided Total Selling Price of the products by No of Product to get Selling Price Per Unit.

  • Then, press ENTER.
  • After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Use of Generic Formula to Calculate Selling Price Per Unit

  • Finally, the Selling Price Per Unit of these products is calculated using the Generic Formula.

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


Practice Section

You will get a dataset like the image given below in this article to practice on your own.

Practice Section


Conclusion

So, in this article, you will find 3 ways to calculate selling price per unit in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin Islam
Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo