Average of duration

Hi,

does anyone know if it is possible to calculate the average of data in a column displaying a duration? They are expressed as 3d, 5d, etc. and therefore it won't let me calculate the average as it doesn't seem to recognize them as numbers.

Thanks,

Barbara

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi Barbara,

Yes, it's possible, but you have to do the calculation in another cell/column.

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

Paul Newcome

If you are using dependencies in Gantt view, the parent rows will automatically provide a summary of your duration. Otherwise you will need to write a formula using a CHILDREN function in a parent row, parsing the letters from the numbers, figuring out your average, then putting numbers and letters back together again. It is very possible though.

Andrée Starå

Paul,

You don't actually need to parse out the letters. It will work as is.

Best,

Andrée

Paul Newcome

I've tried it before, and it would only average out the numbers. It didn't take into account weeks vs days. How did you get it to work, because now I'm curious...?

callie

Hey Andree,

Could you elaborate? I am having trouble with this too. I have a sheet containing the length of all the projects in a portfolio. I would like to calculate the average of all of them.

Thank you!

Andrée Starå

Hi Callie,

You could probably use the Duration feature in Project Settings for the Roll-up sheet.

Did it work?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

I thought the rollup of duration (with dependencies turned on) was a TOTAL time and not an average time?

Andrée Starå

Haha!

Yes, of course, it is. I got confused and mixed it all up!

Shane Burdon

Hey @Paul Newcome & @Andrée Starå - I've been reading this thread and some others that you've commented on about trying to calculate duration but I'm stuck.

I was able to use the formulas that Paul had on another thread to get the duration i.e. 2 hours, 28 minutes. But now I need to calculate the average duration for all columns and I'm stuck.

Example of the columns I added to get duration. Now need to average out all durations listed.

Paul Newcome

@Shane Burdon You would average the column housing the minutes. Then once you generate that average, you can use the same formula previously used to convert the average number of minutes into the Days/Hours/Minutes text string.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…