Average
Hi Leibel Thank you so much for your help yesterday for the formula of Create Date  Modified Date= Time Spent. I wanted to ask if i could convert the results to number Xd : XXh : XXm
Also if you could help me on getting the average in "Sheet Summary" for each month per associate?
I have 9 other associates so I think I should get one result for overall average of all associates.
Thank you.
Best Answer

I haven't tested this, but I presume that @Leibel Shuchat's formula from the other post would work the same here as it did in your sheet:
=IF([Duration Minutes]@row > 1439, ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) + " Day" + IF(ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 1440) > 59, ROUNDDOWN(SUM(MOD([Duration Minutes]@row, 1440) / 60), 0) + " Hour" + IF(SUM(MOD([Duration Minutes]@row, 1440) / 60) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 60) > 0, MOD([Duration Minutes]@row, 60) + " Minute" + IF(MOD([Duration Minutes]@row, 60) > 1, "s", ""), "") + IF([Duration Minutes]@row > 0, ".", "")
The difference is that you would replace the [Duration Minutes]@row reference with the Sheet Summary field reference of [Average]#
Ex:
=IF([Average]# > 1439, ROUNDDOWN(SUM([Average]# / 60) / 24, 0)...etc
Answers

The way to average and /or get totals is to use the 'Durations Minutes' column in the previous post.

hello. it's me again... Can you help with the formula? I've been bouncing my head off the wall.
I want to have the average for the status "Booked" for each month.
Each month date is "End Date" . Thank you....

It sounds like you need an AVG(COLLECT formula instead of AVERAGEIF. AVG(COLLECT allows you to specify more than one criteria.
This is the structure:
=AVG(COLLECT([Column to Avg]:[Column to Avg], [Criteria 1]:[Criteria 1], "Criteria 1", [Criteria 2]:[Criteria 2], "Criteria 2")
So in your case, if I was looking for January's data:
=AVG(COLLECT([Duration Minutes]:[Duration Minutes], Status:Status, "Booked", [End Date]:[End Date], IFERROR(MONTH(@cell), 0) = 1)
Then change out the Month number you're looking for... here's February:
=AVG(COLLECT([Duration Minutes]:[Duration Minutes], Status:Status, "Booked", [End Date]:[End Date], IFERROR(MONTH(@cell), 0) = 2)
Does that make sense?
Cheers!
Genevieve


I haven't tested this, but I presume that @Leibel Shuchat's formula from the other post would work the same here as it did in your sheet:
=IF([Duration Minutes]@row > 1439, ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) + " Day" + IF(ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 1440) > 59, ROUNDDOWN(SUM(MOD([Duration Minutes]@row, 1440) / 60), 0) + " Hour" + IF(SUM(MOD([Duration Minutes]@row, 1440) / 60) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 60) > 0, MOD([Duration Minutes]@row, 60) + " Minute" + IF(MOD([Duration Minutes]@row, 60) > 1, "s", ""), "") + IF([Duration Minutes]@row > 0, ".", "")
The difference is that you would replace the [Duration Minutes]@row reference with the Sheet Summary field reference of [Average]#
Ex:
=IF([Average]# > 1439, ROUNDDOWN(SUM([Average]# / 60) / 24, 0)...etc
Help Article Resources
Categories
Check out the Formula Handbook template!