Microsoft Excel is an advanced computing tool that has some built-in functions, with the help of which user-defined functions can also be extended. This article describes the procedure to set up a measuring cell that calculates the sum of the total time over 30 minutes.
In order to calculate the sum time over 30 minutes in Excel, SUMPRODUCT formula can be used providing it two parameters whose product will give the result. These two parameters must be arrays of the same sizes.
In order to calculate the sum time over 30 minutes in Excel, two arrays can be formulated which are then entered as a parameter in the SUMPRODUCT function.
SUMPRODUCT function returns the sum of the product of arrays fed into it as a parameter. These arrays must be of the same size. This function supports the sum of product of 2 to 255 arrays.
FORMULA OF SUMPRODUCT FUNCTION
=SUMPRODUCT(array1, array2, array3, …)
Parameters of SUMPRODUCT Function
SUMPRODUCT function requires at least two arrays to operate properly. These arrays can be at the maximum 255 in number. The definition of an array of numbers in Excel is given below:
Array(required): A set of numbers of same data type is known as an array.
This function converts hours, minutes and seconds, provided as input, to an Excel serial number formatted with a time format. For instance, hour is a number from 0 to 23.
FORMULA OF TIME FUNCTION
=TIME(hour, minute, second)
Parameters of TIME Function
ARRAY FORMULATION FOR CALCULATING SUM TIME OVER 30 MINUTES
For calculating the sum time over 30 minutes. TIME function must be supplied with the value of 30 minutes as depicted below:
In this example, few time samples are considered randomly. Out of which some are above 30 minutes are some are below. To calculate the sum time over 30 minutes. Following Excel sheet has been considered:
Figure 1. Sample times for calculating sum time over 30 minutes
The Time column is named as an array “times” by selecting all the entries to be entered into the array and clicking in the cell name box (on the left side of the formula bar). Rename it to times and press the enter button.
To calculate the first array named as Array1, the following formula must be used:
In this example, click on the cell D4, enter the formula in the formula bar and extrapolate this formula to the cells below. This will generate an array that represents the difference of time sample with 30 minutes. It can be negative if it is less than 30 minutes and positive if its greater than 30 minutes.
Figure 2. Calculation of Array1 for SUMPRODUCT formula
Similarly, calculate the second array by using the formula:
This will generate a boolean array consisting of entries TRUE or FALSE. If the time is greater than 30 minutes it will result in False and true if the time is greater than 30 minutes. Name the resulted array as Array2.
Figure 3. Calculation of Array2 for SUMPRODUCT formula
SUMPRODUCT formula generates the product of arrays by multiplying array1 and array2, which is shown separately as follows:
Figure 4. Insight to SUMPRODUCT formula that what it gets as the product of two arrays
To calculate the sum time over 30 minutes, SUMPRODUCT formula can be used by providing it Array1 and Array2 as operands. Enter the formula provided below in formula bar.
This will result in the total time sum over 30 minutes in a serial number formatted in Excel’s time format.
Figure 5. SUMPRODUCT formula for calculating sum time over 30 minutes
The total sum time over 30 minutes is shown in cell F15 which can be converted to hh:mm format by formatting a separate or the same cell accordingly. The final result is shown below:
Figure 6. The final result of sum time over 30 minutes in cell F15 using SUMPRODUCT formula