It's Only Blocked Until I Kick It
I have a formula that leverages some sheet summary formulas and returns a true/false to support a report filter. When I change data in the sheet the sheet summary formulas sometimes return a #BLOCKED, but it is not consistent. When it does, if I go into the formula and edit it (without actually changing anything), then the error clears and everything returns to normal. Screenshot:
The [2nd Largest Total Score] formula is =LARGE([Total Score]:[Total Score], [Count of Largest]# +1). If I clear the "+1", it corrects. Then I can add the +1 back in, and it's happy.
Any ideas? Thanks in advance. BTW, all of this is meant as an alternative to solve https://community.smartsheet.com/discussion/72045/usinglargewithduplicateresults
Best Answer

No problem! I'd be interested if it was the INVALID VALUE Blocking the formula, let me know what you find out.
Answers

I can't replicate the error you're seeing, so I wonder if it may be related to the other formulas in your sheet. How is the Total Score being created?
I presume the "Count of Largest" is:
=COUNTIF([Total Score]:[Total Score], [Largest Total Score]#)
Can you try adding a VALUE function around your [Count of Largest]# in the current formula, like so:
=LARGE([Total Score]:[Total Score], VALUE([Count of Largest]#) +1)
Can you also try adding in a new field as a test field to just have =[Count of Largest]# in case this is the field causing the errors? If this also gives you BLOCKED it will help us narrow down the culprit.
Thanks!
Genevieve

Hi @Genevieve P.  This evening I can't seem to replicate the error, either. However, while trying to retrace my steps I did find some errors came when there was only one or two total scores. The 2nd and 3rd largest total score counts produced some #INVALID VALUE. I was able to solve those using an if(iserror(myformula), 0, myformula). I will work with this over the next few days and reach back if I can reproduce the problem. I am also happy to share a copy of the sheet with you if you'd like to tinker with it. Thanks for your help!

No problem! I'd be interested if it was the INVALID VALUE Blocking the formula, let me know what you find out.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!