Formula in metrics sheet to average numbers in column in source sheet
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
Answers
-
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)
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!
-
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
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!