How do I use Averageif when referencing a new sheet?

minh.h.nguyen

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

This is the formula

JamesB

@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

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

@minh.h.nguyen

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

minh.h.nguyen

I know smartsheet is working on a time function

JamesB

@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

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?