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

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.