Average time calculation

Options

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

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    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
    minh.h.nguyen ✭✭✭✭
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    minh.h.nguyen ✭✭✭✭
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    minh.h.nguyen ✭✭✭✭
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    minh.h.nguyen ✭✭✭✭
    Options

    KPH,

    I do appreciate your help. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!