It's Only Blocked Until I Kick It

Scott Peters

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/using-large-with-duplicate-results

Genevieve P.

Hi @Scott Peters

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

Genevieve P.

Hi @Scott Peters

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

Scott Peters

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!

Genevieve P.

Hi @Scott Peters

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