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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!