Average

Options
Marilen.Navarro103391
Marilen.Navarro103391 ✭✭✭✭✭
edited 11/01/21 in Formulas and Functions

@Leibel Shuchat

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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Marilen.Navarro103391

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

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

  • Marilen.Navarro103391
    Options

    @Leibel Shuchat

    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....




  • Genevieve P.
    Options

    Hi @Marilen.Navarro103391

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Marilen.Navarro103391
    Options

    @Genevieve P.


    It works. Thanks. How do I translate this to Days:Hrs:Minutes.... D:h:m


  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Marilen.Navarro103391

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!