How to Calculate Production Per Hour in Excel

Production per hour is a measure of how much output a business or a worker can produce in one hour. It is useful for evaluating the efficiency and productivity of a process, as well as for planning and budgeting purposes. In this article, we will show you how to calculate production per hour in Excel using a simple formula and a table. We will also explain the basic theory behind the calculation and provide a scenario with real data to illustrate the steps.

The formula for calculating production per hour is:

Production per hour=Total output/Total hours worked​

This formula assumes that the output and the hours worked are both measured in the same units, such as units, kilograms, dollars, etc. If they are measured in different units, such as units per hour and hours, then the formula needs to be adjusted accordingly.

For example, if the output is measured in units per hour and the hours worked are measured in hours, then the formula becomes:

Production per hour=Total output per hour×Total hours worked/Total hours worked​

This simplifies to:

$$\text{Production per hour}=\text{Total output per hour}$$

Procedures

To calculate production per hour in Excel, we need to follow these steps:

1. Enter the data for the output and the hours worked in a table. The output can be either the total output or the output per hour, depending on the units used. The hours worked can be either the total hours worked or the hours per unit, depending on the units used.
2. Apply the formula for production per hour to the table using a cell reference or a named range. The formula can be either the basic formula or the adjusted formula, depending on the units used.
3. Format the result as a number, percentage, or any other format that suits the purpose of the calculation.

Example

Let’s say we want to calculate the production per hour of a bakery that produces bread and cakes. The bakery operates for 8 hours a day and has 4 workers. The data for the output and the hours worked are given in the table below:

To calculate the production per hour, we can use the adjusted formula, since the output is measured in units per hour and the hours worked are measured in hours per unit. The formula is:

$$\text{Production per hour}=\text{Output (units per hour)}÷\text{Hours worked (hours per unit)}$$

We can apply this formula to the table using a cell reference or a named range. For example, if the table is in the range A1:C3, we can enter the formula in cell D2 as:

=B2/C2

And copy it down to cell D3. The result is:

We can format the result as a number with no decimal places. The final table looks like this:

Other Approaches

There are other ways to calculate production per hour in Excel, such as using a pivot table, a formula array, or a user-defined function. However, these methods may require more advanced skills and knowledge of Excel. The method we showed in this article is simple and easy to follow, and can be applied to any data set that has the output and the hours worked.