Average time calculation

minh.h.nguyen

What is the formula for finding an average time from another sheet. Please see pics

The avg dwell column is where I want the answer to go. I want the average dwell time for all the SCAC that is associate with CFAA in the first column. the information will be in this sheet

I have tried the averageif formula with the time but do not know how to formulate it. Any help will be appreciate it. Thank you

KPH

Hi @minh.h.nguyen

Are you familiar with setting up cross sheet references?

The syntax for the AVERAGEIF is as follows

=AVERAGEIF({cross reference to SCAC column}, "CFAA", {cross reference for Dwell Time column})

The first reference is the range to look for the criteria in

The next part is the criteria

The final part is the column to take the average from.

Hope this helps.

minh.h.nguyen

KPH,

=AVERAGEIF({scac}, "cfaa", {carrier worksheet dwell time})

this is my formula to pull data from another sheet. it comes back as a zero. how do I use the Time Function with this formula so it pulls correctly?

KPH

Aha! I didn't spot the colons in your Dwell Time column.

I do not believe there is a time function in smartsheet that can do this for you. But you can add another column and use a formula to convert the Dwell Times into a decimal format. Then run the average on the new column (you can hide the column so it doesn't get in your way).

How you convert the format you have into a decimal will depend on what the format you are using at the moment.

It looks like the right hand side is always 2 digits and never more than 60 so I think it could be minutes and seconds or hours and minutes. If so, you could do something like this:

=VALUE(LEFT([Dwell Time]@row, FIND(":", [Dwell Time]@row) - 1)) + (VALUE(RIGHT([Dwell Time]@row, 2)) / 60)

If not, the explanation below should help you adapt this formula to work with your data.

Explanation

Because the first part could be 1 or 2 digits we need to use a combination of LEFT and FIND to extract the part before the colon

=VALUE(LEFT([Dwell Time]@row, FIND(":", [Dwell Time]@row) - 1))

This part will look at the last 2 digits and divides them by 60 to give you a decimal value.

=(VALUE(RIGHT([Dwell Time]@row, 2)) / 60)

Here is what each part of the formula does:

minh.h.nguyen

KPH,

it is hours and minutes. I will convert to a decimal then do my formula. thank you. Last question, I can't do this with the new TIME function that came out recently in smartsheet?

KPH

I have only used the TIME function to do the reverse. You can certainly use it to turn your average into a time.

minh.h.nguyen

KPH,

How do i use the time function to do the reverse? average into time? this is the formula i have =TIME([2]@row, 2, 1)

KPH

Hi @minh.h.nguyen

I am not an expert in the TIME function and have only used it to create time from system values. Someone else might be able to help you here.

I would actually do a quick text manipulation to change your average time into hh:mm format, like this:

=VALUE(LEFT([Average Dwell Time]@row, FIND(".", [Average Dwell Time]@row) - 1)) + ":" + MOD([Average Dwell Time]@row, 1) * 60

This takes the part before the period.

Then places a colon.

Then places the part after the period multiplied by 60.

minh.h.nguyen

KPH,

I do appreciate your help. Thank you!