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

OGSMike

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!

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

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

OGSMike

Thank you @Genevieve P. that worked!