How can I return my value in minutes and seconds?

Kristina Kazakoff

I'm trying to pull the average length of call.

The source sheet is formatted in minutes and seconds:

I'm getting a #DIVIDE BY ZERO error when I apply =AVG. This is caused by the formatting of the values, right? Should I convert the length of call to minutes only before applying the formula? Or is there a solution to use within the formula?

Any input is greatly appreciated!

Paul Newcome

You would need to convert your times into numbers. You could use something along the lines of...

=VALUE(LEFT([Length of Call]@row, 2) + (VALUE(RIGHT([Length of Call]@row, 2) / 60)

Then you could average those numbers. To convert the average back into mm:ss format, you would use something along the lines of...

=IF(INT([Average Number]@row) < 10, "0") + INT([Average Number]@row) + ":" + IF(([Average Number]@row - INT([Average Number]@row)) * 60 < 10, "0"), + ([Average Number]@row - INT([Average Number]@row)) * 60

Kristina Kazakoff

Thanks, Paul, but I'm getting #INVALID OPERATION with that first formula:

What am I missing?

Paul Newcome

My apologies. Misplaced parenthesis.

=VALUE(LEFT([Length of Call]@row, 2)) + (VALUE(RIGHT([Length of Call]@row, 2)) / 60)

Kristina Kazakoff

Thanks for that! I was just able to pick this back up today, and I'm having an issue with the second formula now.

I'm not getting any error messages or zeros, but the cell is just blank when I click out of the formula box?

Paul Newcome

It looks like you have a misplaced parenthesis. Take the closing parenthesis off of the end and tuck it in right after the first "0".

Kristina Kazakoff

I'm getting #INCORRECT ARGUMENT SET now?

Paul Newcome

It is another misplaced parenthesis. Try using the formula I posted above. Copy/paste it, and you should only have to change the column names.

Kristina Kazakoff

That's what I had tried initially, but it returned #UNPARSEABLE. I thought maybe it wasn't reading it correctly because I hadn't clicked the source column?

I just pasted and swapped out the column name now and got the same result:

Paul Newcome

Hmm... Depending on your region, you may need to replace the commas with semi-colons...

=IF(INT([Average Number]@row) < 10; "0") + INT([Average Number]@row) + ":" + IF(([Average Number]@row - INT([Average Number]@row)) * 60 < 10; "0"), + ([Average Number]@row - INT([Average Number]@row)) * 60

Give that a try...

Kristina Kazakoff

No dice 😔

L_123

I think it is easier to convert to seconds instead of minutes. Also if it goes over 99 minutes the formula will fail.

For the helper column you can use

=VALUE(LEFT([Length of Call]1, FIND(":", [Length of Call]1) - 1)) * 60 + VALUE(RIGHT([Length of Call]1, 2))

Then you can use the modulus formula to simplify the second expression

=ROUNDDOWN(AVG([Minutes Only]:[Minutes Only]) / 60) + ":" + MOD(AVG([Minutes Only]:[Minutes Only]), 60)

Paul Newcome

@L@123 is correct. My solution will not work for times greater than 99 minutes because of the first part (the helper column).

I haven't tested the second part of L's solution yet, but I would venture to say you may end up wanting to make some slight adjustments. The reason my second part is longer is because if the average turns out to be 9 minutes and 4 seconds, it will be displayed as

9:4

I threw in some IF statements to make it display as

09:04

You may not be as worried about the zero in front of the minutes

9:04

But not having the zero in front of the 4 would probably get people asking questions. To have

9:04

you would need to tuck in an IF statement like so:

=ROUNDDOWN(AVG([Minutes Only]:[Minutes Only]) / 60) + ":" + IF(MOD(AVG([Minutes Only]:[Minutes Only]), 60) < 10, "0") + MOD(AVG([Minutes Only]:[Minutes Only]), 60)

And typing up the above showed me why my second portion wasn't working for you. I had an errant comma tucked in.

=IF(INT([Average Number]@row) < 10, "0") + INT([Average Number]@row) + ":" + IF(([Average Number]@row - INT([Average Number]@row)) * 60 < 10, "0") + ([Average Number]@row - INT([Average Number]@row)) * 60

Kristina Kazakoff

Success! I used L@123's formula suggestions with the added IF statement and it all appears to be totaling up correctly.

Thank you both.

Paul Newcome

Great! Happy to help! 👍️