Formula in metrics sheet to average numbers in column in source sheet

Lori Flanigan
Lori Flanigan ✭✭✭✭✭
in Formulas and Functions

I have a source sheet containing two date columns and a helper column containing a formula to calculate the network days between the date columns. (Example of several rows of my 600+ row source sheet.)

image.png


In my metrics sheet, I want to calculate the average of the Days to Complete Termination column in my source sheet and I don't want the rows with 0 to be included in the average calculation.

I've looked in the Community and found similar questions. I've tried =AVERAGEIF([OFF-DaysToCompleteTerm]:[OFF-DaysToCompleteTerm], <>0, [OFF-DaysToCompleteTerm]:[OFF-DaysToCompleteTerm]) and it returns #UNPARSEABLE.

Thanks for any help on this.

Lori Flanigan

Tags:
· Share on FacebookShare on Twitter

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Lori Flanigan

    Since you are using this formula in a metrics sheet, you need to create a reference to the column in the source sheet.

    Start typing your formula. When you get to =AVERAGEIF( a helper box will pop up. Click on "Reference Another Sheet", navigate to your source sheet, and select the column header for the OFF-DaysToCompleteTerm. The system will select a range name at the top of the screen which you can edit if desired (I usually abbreviate the column name after the source sheet name.) This will create a reference in your formula that looks like this: {Source Sheet Name Range 1}.

    image.png
    image.png


    Otherwise your syntax looks good. Since you're averaging the column you have criteria for, you don't need to list the range a second time. Also, you can use > 0 as your criteria:

    =AVERAGEIF({Source Sheet Name OFF-DaysToCompleteTerm}, > 0)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • Lori Flanigan
    Lori Flanigan ✭✭✭✭✭

    Hi Jeff,

    Thanks for your response.

    The OFF- is my shorthand reference to the source sheet -- sorry I wasn't clear about that in my original post.

    I updated my formula to what you have at the end and it does work. Removing the last part and changing the straight brackets to curly brackets were the issues, it seems.

    Thank you for answering another question of mine!

    Lori

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Lori Flanigan I'm always happy to help. Glad you got it working!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!