RANKEQ showing multiple 1 Ranks but still showing a rank of 2

I'm running into an issue using RANKEQ to determine the top 10 categories with some criteria while working with census data. The formula I'm using is:

=IFERROR(RANKEQ([Speak English less than "Very Well"]@row, COLLECT([Speak English less than "Very Well"]:[Speak English less than "Very Well"], County:County, =County@row, Hierarchy:Hierarchy, Hierarchy@row = 0)), "")

This works for most of my data set, however, for two Counties there are multiple ranks of 1 along with a rank of 2 which I cannot determine a cause for (see example)

I'm not sure why the hierarchy being set a "1" for Spanish and Spanish Creole line is getting counted as 1 along with Spanish with the hierarchy set a zero.

Any insight the community would have would be much appreciated!

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @OGSMike

Do you have "Spanish and Spanish Creole" anywhere else in the sheet?

The Collect function isn't looking at this specific row for the 0 Hierarchy. It's looking to see if this "speak english" value appears anywhere in the sheet with this row's county and with 0, then ranking this row against those rows.

I would suggest adding an IF statement at the front, like so:

=IFERROR(IF(Hierarchy@row <> 0, "", RANKEQ([Speak English less than "Very Well"]@row, COLLECT([Speak English less than "Very Well"]:[Speak English less than "Very Well"], County:County, County@row, Hierarchy:Hierarchy, 0))), "")

Cheers!

Genevieve

All comments

Genevieve P.

Hi @OGSMike

Do you have "Spanish and Spanish Creole" anywhere else in the sheet?

I would suggest adding an IF statement at the front, like so:

Cheers!

Genevieve

OGSMike

Thank you @Genevieve P. that worked!

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.