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

Lori Flanigan

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.)

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

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}.

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)

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

Jeff Reisman

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