How do I use Averageif when referencing a new sheet?

Options
minh.h.nguyen
minh.h.nguyen ✭✭✭✭
edited 11/09/23 in Formulas and Functions

=AVERAGEIF(OTP 10.21.23 SCAC Range), "CFAA", (OTP 10.21.23 dwell TIME)


This is the formula

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @minh.h.nguyen

    It does not look like your formula is referencing another sheet. If it was there would be brackets around the range name.

    =AVERAGEIF({OTP 10.21.23 SCAC Range}), "CFAA", ({OTP 10.21.23 dwell TIME})

    When creating your formula, and you get to the point of typing =AVERAGEIF( , you will have a pop up on the screen for the usage of the formula. On this will be the option to Reference Another Sheet underlined and in blue. Use this link to create your sheet references.

  • minh.h.nguyen
    minh.h.nguyen ✭✭✭✭
    Options

    James, this is the new formula =AVERAGEIF({OTP 10.21.23 SCAC Range}, "CFAA", {OTP 10.21.23 time}). It came back as a 0 but, that is not the right number. Can you help?

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @minh.h.nguyen

    Without showing any confidential data, can you share a screenshot of your reference data?

  • minh.h.nguyen
    minh.h.nguyen ✭✭✭✭
    Options

    I know smartsheet is working on a time function

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    @minh.h.nguyen

    I just recreated the data in my own sheet and tested your formula. The issue seems to be the Colon. When I change it to a whole number or a decimal it is working. On your DWELL time you could use the SUBSITUTE function to replace the Colon with a decimal, and then use the substitute again to change it back to a Colon.

    =SUBSTITUTE(AVERAGEIF(SCAC:SCAC, ="LTGA", DWELL:DWELL), ".", ":")

  • minh.h.nguyen
    minh.h.nguyen ✭✭✭✭
    Options

    James, I tried your formula =SUBSTITUTE(AVERAGEIF(SCAC:SCAC, ="LTGA", DWELL:DWELL), ".", ":"). For some reason this does not work. Is there a way to change my dwell time to a decimal in my worksheet before running the formula? I also tried this formula because I am grabbing information from another sheet =SUBSTITUTE(AVERAGEIF({OTP 10.21.23 SCAC Range}, "LTGA", {OTP 10.21.23 time})). Do I use the substitute function in this formula for the dwell time? If so, where do I put it?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!