This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 49%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECW%3C/text%3E%3C/svg%3E)
I have one column of averaged times that need to have the "weighted average" for this same column at the bottom where answer displays in this one cell
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 49%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJT%3C/text%3E%3C/svg%3E)
Hi @Carrie Ward,
I sincerely apologize for any confusion caused earlier regarding the average handle time calculation. The formula I initially provided calculates a simple average, which assumes equal importance for each time entry.
To accurately compute a weighted average, we’ll need an additional set of data: the weights associated with each time value. These weights typically represent the number of cases, frequency, or any other metric that reflects the relative significance of each entry.
Could you kindly share the corresponding weights for each time value in your dataset? Once received, I’ll be happy to assist you with the correct weighted average formula and implementation in Excel.
Thank you for your understanding, and I look forward to your response.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
The weight for each row is simply the second column divided by the first. You could enter the following formula in C1
=IF(B1=0,1,B1/A1)
and copy it down to the last row of data.
I have never seen anyone ask for the average of the weights which is completely different than the weighted average of the data.. In any case the formula
=AVERAGE(C1:C20)
will compute the arithmetic mean of the 20 weights.
Hi @Carrie Ward,
Thank you for the update. Here is the updated version of calculating weighted average:
Assuming your ASA Weight values are in column B starting from cell B2, enter the following formula in column C to convert each time to seconds:
=MINUTE(B2)*60 + SECOND(B2)
Copy this formula down for all rows containing ASA Weight values.
In a separate cell (e.g., C17), use the following formula to calculate the average:
=AVERAGE(C2:C16)
To display the average in a readable format (e.g., 8m 20s), use this formula in another cell (e.g., D17):
=INT(C17/60) & "m " & MOD(C17,60) & "s"
If your time values are not recognized as time format (e.g., entered as text like "02:14" or "2;14"), you may need to first convert them to proper time format using Excel's TIME function or by replacing semicolons with colons.
This is the final result:
If you have any further questions and concerns, please let me know. looking forward to your response.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
JayYou are treating the values as mm:ss but Excel is treating them as hh:mm
If you look at row 3, 4m21s should be 261s or 4h21m should be 15660s. It should never be 1260s which is just 21m.
Additionally, applying AVERAGE to the weighted values does not produce the weighted average as the term is used in mathematics. AVERAGE uses the formula sum/count but a weighted average is calculated as sum/(sum of weights).