I'm getting #NoMatch error on AVERAGEIF
I am using the Smartsheet JIRA connector to pull data into my sheet then I'm performing multiple calculations to get the % Complete for Stories (triple weighted score). I'm taking that calculation then rolling it up to the Epic (for all Stories) then the Theme (for all Epics).
Formula for rolling up Stories to Epics:
=IF([Issue Type]@row = "Epic", ROUND(AVERAGEIF([Epic Link]:[Epic Link], [Issue Key]@row, [PC - Work]:[PC - Work]), 2), "--")
I'm creating a sheet for each Theme, this formula works great on 98% of the sheets, however every once in a while I'm getting a sheet that throws a #NOMATCH error.
Working:
Rollup Columns
Doesn't Work
Rollup Columns
The questions I have are:
1) Why does it work most of the time but fail periodically?
2) What can I do to keep this from happening?
It's happening on some other columns with COUNTIFS too but I'm thinking that the same issue as described as above is happening there too. I'm hoping that if I can resolve this issue the solution will help to resolve those issues too.
Best Answer
-
Never mind, I found the issue. It was buried in another Epic that was throwing the error but based on the formulas it causes all Epic to throw the error.
Answers
-
Never mind, I found the issue. It was buried in another Epic that was throwing the error but based on the formulas it causes all Epic to throw the error.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!