Average

Marilen.Navarro103391

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

Genevieve P.

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

Leibel S

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

Marilen.Navarro103391

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

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

Marilen.Navarro103391

@Genevieve P.

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

Genevieve P.

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