Mrinmoy Roy
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.
Download the Practice Workbook
You can download the Excel file from the following link and practice along with it.
4 Ways to Calculate Production per Hour in Excel
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 EfficiencyTo 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.
❸ 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: Excel formula to calculate hours worked & overtime [with template]
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.
❸ 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 Elapsed Time in Excel (8 Ways)
Similar Readings:
- How to Subtract Military Time in Excel (3 Methods)
- Subtract and Display Negative Time in Excel (3 Methods)
- How to Calculate Hourly Rate in Excel (2 Quick Methods)
- Use Time Format in Excel VBA (Macro, UDF, and UserForm)
- How to Calculate Average Turnaround Time in Excel (4 Methods)
3. For Multiple Factories Calculate 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 FactoriesNow, to calculate 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:
Related Content: How to Calculate Total Hours in Excel (9 Easy Methods)
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:
Related Content: How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)
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. And please visit our website Exceldemy to explore more.
