How to Calculate Production per Hour in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

The calculation of the production per hour is a good way to monitor the production growth of a company. Based on the production per hour result, one can take necessary steps to improve mass production. There are many ways available that you can use to calculate production per hour in Excel. In this article, you will get to know 4 easy ways that you can use right away to calculate production per hour in Excel with ease.


How to Calculate Production per Hour in Excel: 4 Ways

1. Calculate Estimated Production per Hour Using Target Efficiency and Working Period

Consider a productive house with 6 working units. Each unit has its own machine efficiency.

Now each production house sets its own target efficiency before jumping into mass production.

To calculate the production per hour, you can record the total production hour. Then you can divide the total production unit by the total production hour. This is one way to calculate production per hour.

Alternatively, you can use formulas to estimate production per hour by observing a certain period of time.

To calculate production per hour using a formula, you need the following information:

  • Target Efficiency
  • Working Period
  • Machine Efficiency

Now you can use the following formula to calculate production per hour.

Production per Hour = (Target Efficiency x Working Period) x Machine Efficiency

To calculate production per hour in Excel,

❶ Insert the following formula in cell E7.

=ROUND(($D$4/D7)*C7*100,2)

In the formula above,

  • $D$4 contains the Target Efficiency.
  • D7 supplies the Working Period.
  • C7 contains Machine Efficiency.
  • The ROUND function is used to round up the formula result to 2 decimal places.

❷ Now press the ENTER button to execute the formula.

Calculate Estimated Production per Hour Using Target Efficiency and Working Period

❸ Drag the Fill Handle icon from cell E7 to E12.

Now you will see the estimated production per hour for all the units of the production house.

Read More: How to Calculate Total Hours Worked in a Week in Excel


2. Calculate Production per Hour Using Total Production Units and Elapsed Time

To calculate production per hour using this method, you need the following information:

  • Total Production Units Produced
  • Total Elapsed Time

With that information in hand, to calculate production per hour in Excel, go through the following steps:

❶ Select cell E5 and insert the following formula:

=ROUND(C5/D5,0)

In the formula above,

  • C5 carries Total Production Units.
  • D5 represents the Total Elapsed Time for the production in hours.
  • The ROUND function is used to round up the formula result.

❷ Now press ENTER to execute the formula.

Calculate Production per Hour Using Total Production Units and Elapsed Time

❸ Drag the Fill Handle icon from cell E5 up to E10 to apply the same formula to all the cells.

After that, you will see the calculated production per hour for all the units as in the image below:

Read More: How to Calculate Hours Worked Minus Lunch with Excel Formula


3. For Multiple Factories Calculate the Estimated Production per Hour

The formula to calculate estimated production per hour for multiple factories is,

Production per Hour = (Target Efficiency x Working Period) x Machine Efficiency x No. of Factories

Now, to calculate the estimated production per hour in Excel,

❶ Select cell F7 to insert the following formula:

=ROUND(($D$4/D7)*C7*E7*100,0)

In the formula above,

  • $D$4 contains the Target Efficiency.
  • D7 supplies the Working Period.
  • C7 contains Machine Efficiency.
  • E6 represents the No. of Factories.
  • The ROUND function is used to round up the formula result.

❷ After that press the ENTER button to execute the formula.

❸ Then drag the Fill Handle icon from cell F7 to F12.

After all of these steps, you will see the estimated production per hour for all the units as in the image below:

For Multiple Factories Calculate Estimated Production per Hour

Read More: How to Calculate Hours and Minutes for Payroll Excel


4. For Multiple Factories Calculate Production per Hour Using Total Production Units and Elapsed Time

To calculate production per hour using the total production units and total elapsed time for multiple factories in Excel,

❶ Select cell F5 to insert the following formula:

=ROUND(C5*E5/D5,0)

In the formula above,

  • C5 represents Total Production Units.
  • E5 represents No. of Factories.
  • D5 represents the Total Elapsed Time for the production in hours.
  • The ROUND function is used to round up the formula result.

❷ After that hit the ENTER button.

❸ Now drag the Fill Handle icon to the end of the Production per Hour column.

After all the steps above, you will see the calculated production per hour amount for all the units as in the picture below:

For Multiple Factories Calculate Production per Hour Using Total Production Units and Elapsed Time

Related Content: How to Calculate On Time Delivery Performance in Excel


Download the Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed 4 ways to calculate production per hour in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.


Related Articles


<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo